topic Set Analysis vs MDX Statement in QlikView

Set Analysis vs MDX Statement

Mon, 29 Oct 2012 08:35:21 GMT

Dear Colleagues,

How do QV Set analysis (please explain with syntax" produce the same result as per below MDX Statement?

([YearMonth].CurrentMember, [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(1), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(2), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(3), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(4), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(5), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(6), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(7), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(8), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(9), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(10), [Measures].[Months])

+ ([YearMonth].CurrentMember.Lag(11), [Measures].[Months])

NB: "YearMonth" is a dimension based on which the above calc should work out. "Month" is a measure.

Thank you for your help.

Gysbert_Wassenaar — Mon, 29 Oct 2012 08:58:27 GMT

I think your mdx could also be written as sum(([YearMonth].CurrentMember.Lag(11), [Measures].[Months])). The sum of Months over the last 12 months.

The set analysis expression would be something like this:

sum({<YearMonth={"<=$(=only(YearMonth)>$(=Only(YearMonth)-12))"}>} Months)

The sum of Month where YearMonth is <= the selected YearMonth and > (YearMonth - 12 months)

Mon, 29 Oct 2012 09:31:15 GMT

Thank you so much Gysbert.