0

I'm trying to create a measure that holds the prior period value

Normally what you do is: 

WITH Member Measures.PrevAmt AS ([Time].[Calendar].CurrentMember.Lag(1),Measures.Amount)
SELECT {Measures.Amount,MEasures.PRevAmt} on 0
,{[Time].[Calendar].[year]
,[Time].[Calendar].[quarter]
,[Time].[Calendar].[Month]} on 1
from [sales]

works fine, but i only want to limit it to a particular year. Easy, add Where ([Time].[Year].[2008])

but the resultset returns null for prevamt where the .lagged member would be in 2007 (eg at the year level and and the 1st member of each subsequent level). SSAS MDX returns the expected result and there's nothing in the IcCube doco that says that .lag/parallelperiod etc functions execute after the slicer is applied and the final resultset is created...

I get the expected results if i hack something together like this

WITH Member Measures.PrevAmt AS ([Time].[Calendar].CurrentMember.Lag(1),Measures.Amount)
SELECT {Measures.Amount,MEasures.PRevAmt} on 0
,{[Time].[Calendar].[2008]
,descendants([Time].[Calendar].[2008],1)
,descendants([Time].[Calendar].[2008],2)} on 1
from [sales]

but this breaks when we slice by an attribute that isn't in the select list (and this code is ugly and probably slower)

Am i missing something?

jkmelbs
  • 444
  • 5
  • 14
  • Can you add the MDX request that is creating the issue, you can't have the same hierarchy in axis and where clause (I'm a bit puzzled) – ic3 Sep 22 '16 at 07:42
  • running the 1st query, we can see that for 2008 the measures are {Amount=586,350,PrevAmt=560,250}. But when you run "WITH Member Measures.PrevAmt AS ([Time].[Calendar].CurrentMember.Lag(1),Measures.Amount) SELECT {Measures.Amount,MEasures.PRevAmt} on 0 ,{[Time].[Calendar].[year] ,[Time].[Calendar].[quarter] ,[Time].[Calendar].[Month]} on 1 from [sales] WHERE [Time].[Year].[2008]" PrevAmt is null for 2008 – jkmelbs Sep 23 '16 at 00:34
  • got it ........ – ic3 Sep 23 '16 at 08:27
  • Indulge me if I'm wrong this is how 'Attributes' are working in SSAS that is a concept that is not present in icCube – ic3 Sep 23 '16 at 09:11
  • Yes, this is how attributes/single level hierarchies work in SSAS dimensions – jkmelbs Oct 02 '16 at 22:54

0 Answers0