Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alip_nath
Contributor

Dynamic Rolling Sum - 4 Quarters trailing

Hello Folks,

I have a requirement to compute rolling sum for the trailing quarters with a dynamic range from Current Quarter till the Last Quarter of the minimum Fiscal Year in the data set. For  an example, if the data set starts from '2013 Q 1' till '2018 Q 1' then the trailing quarters should be displayed from 2017 Q 4 till 2013 Q 4. Using a formula like '' works but does not take care the start and end of the  quarter:

'rangesum( above ( sum([Reported PEB]),0,4) )'

Any thoughts to achieve this. See below highlighted in green is desired and yellow highlighted is what I get now.

Trailing Quarter.JPG

1 Solution

Accepted Solutions

Re: Dynamic Rolling Sum - 4 Quarters trailing

From the data you provided you can do this

=If(Only({<FQ>} FQNum) >= Min({1}TOTAL  FQNum, 4) and Only({<FQ>} FQNum) <= Num#(Date(MonthStart(Today()), 'YYYYMM'), '##'), RangeSum(Above(Sum({<FQ>}[Reported PEB]), 0, 4)))

But there are some better ways to do this if you have other fields such as MonthYear...etc....

Capture.PNG

12 Replies

Re: Dynamic Rolling Sum - 4 Quarters trailing

So you would want to only see the green cells because those are completed quarters and have last 4 quarter available to calculate 12 Month Trailing?

Also, do you have MonthField in your dashboard or do you just use Today() to determine if the quarter is complete or not?

alip_nath
Contributor

Re: Dynamic Rolling Sum - 4 Quarters trailing

Yes for the first part of your reply.


For the second part not sure of the entire requirement but required calendar columns(month,etc) can definitely be added.

Re: Dynamic Rolling Sum - 4 Quarters trailing

From the data you provided you can do this

=If(Only({<FQ>} FQNum) >= Min({1}TOTAL  FQNum, 4) and Only({<FQ>} FQNum) <= Num#(Date(MonthStart(Today()), 'YYYYMM'), '##'), RangeSum(Above(Sum({<FQ>}[Reported PEB]), 0, 4)))

But there are some better ways to do this if you have other fields such as MonthYear...etc....

Capture.PNG

alip_nath
Contributor

Re: Dynamic Rolling Sum - 4 Quarters trailing

Thanks stalwar1‌ Can you share a few ideas with calendar functions to achieve this?

Re: Dynamic Rolling Sum - 4 Quarters trailing

Is this the only date and time related field do you have? No dates or MonthYear in your dashboard?

alip_nath
Contributor

Re: Dynamic Rolling Sum - 4 Quarters trailing

Also can you elaborate on the column FQnum?

Re: Dynamic Rolling Sum - 4 Quarters trailing

Just a numeric representation of FQ created in script

SubField(FQ, ' Q ', 1)*100 + SubField(FQ, ' Q ', 2)*3 as FQNum,

alip_nath
Contributor

Re: Dynamic Rolling Sum - 4 Quarters trailing

Not at the moment, however, a date dimension can be introduced.

Hence, I am more eager to have a better solution that incorporates calendar function to achieve this.

Luminary
Luminary

Re: Dynamic Rolling Sum - 4 Quarters trailing

Hello Alip,

I recommend this approach quite often and I think it can be used in your case as well: The As-Of Table

Juraj

Community Browser