Document toolboxDocument toolbox

Optimizing query times with complex calculated measures

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)


The following table lists the MDX expressions that are supported from Kyvos 4.2 version onwards, and the MDX expressions that are supported on all Kyvos versions.

Time to Period

MDX Expressions for Kyvos 4.2 and above

MDX Expression supported for all kyvos versions

Time to Period

MDX Expressions for Kyvos 4.2 and above

MDX Expression supported for all kyvos versions

YTD

iif([Calendar].[Calendar].CurrentMember is [Calendar].[Calendar].DefaultMember , null ,
(Sum((PeriodsToDate([Calendar].[Calendar].[Year],[Calendar].[Calendar].CurrentMember)),[Measures].[Received Qty])))

 

IIF( [Calendar].[Calendar].currentmember.level_number < 1,
null,
IIF( [Calendar].[Calendar].currentmember.level_number = 1,
sum([Calendar].[Calendar].currentmember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 2,
sum([Calendar].[Calendar].currentmember.parent.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 3,
sum([Calendar].[Calendar].currentmember.parent.parent.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 4, sum([Calendar].[Calendar].currentmember.parent.parent.parent.FirstChild.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),  sum([Calendar].[Calendar].currentmember.parent.parent.parent.parent.FirstChild.FirstChild.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty])
    )
   )
   )
  )
)

QTD

iif( [Calendar].[Calendar].Currentmember.level_number < 2, Sum( [Calendar].[Calendar].Currentmember ,[Measures].[Received Qty]),
(Sum((PeriodsToDate([Calendar].[Calendar].[Quarter],[Calendar].[Calendar].CurrentMember)),
[Measures].[Received Qty])))

 

IIF( [Calendar].[Calendar].currentmember.level_number <= 2,
sum([Calendar].[Calendar].currentmember,
[Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 3,
sum([Calendar].[Calendar].currentmember.parent.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 4,  sum([Calendar].[Calendar].currentmember.parent.parent.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]), sum([Calendar].[Calendar].currentmember.parent.parent.parent.parent.FirstChild.FirstChild.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty])
           )
    )
    )

MTD

iif( [Calendar].[Calendar].Currentmember.level_number < 3,
Sum( [Calendar].[Calendar].Currentmember ,[Measures].[Received Qty]),
(Sum((PeriodsToDate([Calendar].[Calendar].[Month],[Calendar].[Calendar].CurrentMember)),
[Measures].[Received Qty])))

 

IIF( [Calendar].[Calendar].currentmember.level_number <= 3,  sum([Calendar].[Calendar].currentmember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 4,
sum([Calendar].[Calendar].currentmember.parent.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),  sum([Calendar].[Calendar].currentmember.parent.parent.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty])
    )
  )

WTD

iif( [Calendar].[Calendar].Currentmember.level_number < 4,
Sum( [Calendar].[Calendar].Currentmember ,[Measures].[Received Qty]),
(Sum((PeriodsToDate([Calendar].[Calendar].[Week],[Calendar].[Calendar].CurrentMember)),
[Measures].[Received Qty])))

 

IIF( [Calendar].[Calendar].currentmember.level_number <= 3,
sum([Calendar].[Calendar].currentmember, [Measures].[Received Qty]),
IIF( [Calendar].[Calendar].currentmember.level_number = 4,     sum([Calendar].[Calendar].currentmember.parent.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty]),         sum([Calendar].[Calendar].currentmember.parent.parent.FirstChild.FirstChild : [Calendar].[Calendar].currentMember, [Measures].[Received Qty])
    )
 )

 

Copyright Kyvos, Inc. All rights reserved.