# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor II

## As-of table for rolling 12 months

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.

Labels (2)

• ### Rolling 12 months

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: As-of table for rolling 12 months

Hey Divya -

You can try this

=If(
AsOfMonth <= Floor(MonthEnd(Max(TOTAL NumDate))) and
, AsOfMonth)

But there are better ways to do this then doing this....

6 Replies
Contributor II

## Re: As-of table for rolling 12 months

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.

Highlighted
MVP

## Re: As-of table for rolling 12 months

Hey Divya -

You can try this

=If(
AsOfMonth <= Floor(MonthEnd(Max(TOTAL NumDate))) and
, AsOfMonth)

But there are better ways to do this then doing this....

Contributor II

## Re: As-of table for rolling 12 months

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

## Re: As-of table for rolling 12 months

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

Contributor II

## Re: As-of table for rolling 12 months

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.

MVP

Sounds good...