Rolling 4 quarters and previous rolling 4 quarters

[laurafinn]

Hi all,

I've attached dummy data to demonstrate what I'm trying to achieve.  I need to be able to present a count of IDs for the last rolling 4 quarters and include a comparison of the previous rolling 4 quarters.

So current would be all activity between Q2 2020 and Q1 2021

Previous is Q1 2020 and Q4 2020

I am already using a temp calendar and have reviewed the posts on "As of Calendars" here https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130 , I like the approach of the "As of" calendar

But I'm struggling with:

-How to implement an As of calendar that distinguishes quarters

-How I'd then use that in a set expression so the dashboard automatically refreshes the calculations when we move into a new quarter

[jwjackso · Accepted solution]

I believe the As-Of-Table is the correct approach.

QuarterDiff=(Ceil((Month([AsOfMonth]) - Month([Month]))/3))+ ((Year([AsOfMonth]) - Year([Month])) * 4)

RollingQuarters = Count({<QuarterDiff={"<4"}>}ID)

PreviousRollingQuarters = Count({<QuarterDiff={">3<8"}>}ID)

[laurafinn]

Thank you!  I substituted Ceil for floor because there was a slight difference between the dummy and real data but this worked a treat.