12 Replies Latest reply: Jan 17, 2018 3:50 PM by Sunny Talwar

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

• ###### 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?

• ###### Re: Dynamic Rolling Sum - 4 Quarters trailing

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

• ###### 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?

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

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

• ###### Re: Dynamic Rolling Sum - 4 Quarters trailing

That is good to know, but is your data always coming in as Quarterly number? Would you be able to share few rows of raw data? Trying to make sense of what you might have

• ###### 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,

• ###### Re: Dynamic Rolling Sum - 4 Quarters trailing

Off-topic, but what does this statement mean?

```Only({<FQ>} FQNum) >= Min({1}TOTAL  FQNum, 4)
```

I think it means "If there is one FQNum selected, regardless of the FQ,

and that value is greater or equal to

the least of the 4 FQNums of the entire dataset regardless of selection,

then...

• ###### Re: Dynamic Rolling Sum - 4 Quarters trailing

If there is one FQNum selected

Doesn't have to be selected.... FQ and FQNum are one to one and that is why I used Only().... it doesn't mean that one FQNum is selected