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]) ) )