# 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
Did you mean:
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.

- Eager to learn n grow,

Olip
1 Solution

Accepted Solutions
MVP

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

12 Replies
MVP

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?

Creator
Author

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
MVP

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

Creator
Author

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

- Eager to learn n grow,

Olip
MVP

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

Creator
Author

Also can you elaborate on the column FQnum?

- Eager to learn n grow,

Olip
MVP

Just a numeric representation of FQ created in script

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

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

Tags
Community Browser