Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create a rolling 12 months accumulation on a table. I've figured out that, As-of table is a potential solution and I tried implementing as described here. This works for the calculation, However, I need help in displaying just the marked rows in the below screenshot when Year=2018 & Month=Oct is selected (i.e., Rolling 12 months).
Any idea?
Thank you.
Hey Divya -
You can try this
=If(
AsOfMonth <= Floor(MonthEnd(Max(TOTAL NumDate))) and
AsOfMonth >= Monthstart(AddMonths(Max(TOTAL Date), -11))
, AsOfMonth)
But there are better ways to do this then doing this....
I tried and got a bit closer. PFA the qvw. The "Works" table is exactly what i am looking for (ref to the screenshot). This was achieved by providing a calculated condition like this:
=if(Floor(Date#(AsOfMonth,'YYYY MMM'))<=43374 and Floor(Date#(AsOfMonth,'YYYY MMM'))>=43040, AsOfMonth)
However, this is not dynamic, so i replaced the NumDate values with expression as given below to replicate rolling 12 months only and this doesnt work (screenshot "Not Working" table)
=if(Floor(Date#(AsOfMonth,'YYYY MMM'))<=floor(num(MonthEnd(max(NumDate) ) )) and Floor(Date#(AsOfMonth,'YYYY MMM'))>=num(Monthstart(AddMonths(max(Date),-11))), AsOfMonth)
I also tried using variables with no luck. Any idea how I could make this work?
Thank you.
Hey Divya -
You can try this
=If(
AsOfMonth <= Floor(MonthEnd(Max(TOTAL NumDate))) and
AsOfMonth >= Monthstart(AddMonths(Max(TOTAL Date), -11))
, AsOfMonth)
But there are better ways to do this then doing this....
With AsOfTable, but using set analysis instead of using a calculated dimension.
Hi Sunny,
Yes, got it. Let me try and if I come up with a solution using Set analysis I will post it here.
Thank you.
Sounds good...