Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Olip
Creator
Creator

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

- Eager to learn n grow,

Olip
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

12 Replies
sunny_talwar

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?

Olip
Creator
Creator
Author

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.

- Eager to learn n grow,

Olip
sunny_talwar

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

Olip
Creator
Creator
Author

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

- Eager to learn n grow,

Olip
sunny_talwar

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

Olip
Creator
Creator
Author

Also can you elaborate on the column FQnum?

- Eager to learn n grow,

Olip
sunny_talwar

Just a numeric representation of FQ created in script

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

Olip
Creator
Creator
Author

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.

- Eager to learn n grow,

Olip
juraj_misina
Luminary Alumni
Luminary Alumni

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