As-of table for rolling 12 months

[divya_anand]

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.

[divya_anand]

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.

[sunny_talwar · Accepted solution]

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....

[divya_anand]

Hi Sunny,
Thank you very much. It works perfect.
When you say "better ways", do you mean it can be done without using As-of Calendar? I tried doing it without the As-of table but couldn't do it. If you have a solution already in mind without the use of As-of calendar, It would be great if you could share it.

Thank you again.

[sunny_talwar]

With AsOfTable, but using set analysis instead of using a calculated dimension.

[divya_anand]

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.

[sunny_talwar]

Sounds good...