topic Re: Rolling 12 Months Issue in App Development

Rolling 12 Months Issue

Rehan — Thu, 28 Mar 2019 17:39:50 GMT

I am trying to calculate the rolling 12 months by using the below expression. Where Month is'MMM YYYY' Format. Its not giving me the correct totals. Am I missing anything here?

Sum(Aggr(RangeSum(Above( Sum(Sales), 0, 12)),Month))

Re: Rolling 12 Months Issue

Oleg_Troyansky — Thu, 28 Mar 2019 19:17:12 GMT

Your formula appears correct, if your Month field is sorted properly. If it's a string (not a date field) formatted as 'MMM YYYY' then it's not sorted properly. Use a proper date field, loaded in chronological order, and it should work.

Alternatively, you can use a sorted AGGR() formula. I'm not going to try and quote the exact syntax from memory, but you can look it up in the help article for AGGR().

I'll be teaching these topics (AGGR, Set Analysis, etc.) next Tuesday in Stockholm, at the upcoming Masters Summit for Qlik event. It's not too late to secure your seat!

Cheers,

Oleg Troyansky

Re: Rolling 12 Months Issue

Rehan — Thu, 28 Mar 2019 19:24:38 GMT

I just reloaded it with MOnrh formatted as Date and now the Month format is 'MM/DD/YYYY'. Still the same issue

Re: Rolling 12 Months Issue

Oleg_Troyansky — Thu, 28 Mar 2019 20:09:17 GMT

It's hard to guess what might be wrong without seeing anything other than the formula (which looks valid to me). If you can create a small sample app and post it here, I could possibly review and tell you what is wrong there.

Re: Rolling 12 Months Issue

pradosh_thakur — Fri, 29 Mar 2019 07:28:11 GMT

Well you have to tell what your dimension is ? If it is Month then you don't even need the AGGR() SORT it in ascending order by Month and rangesum(above(sum(Sales),0,12)) will do.

-Pradosh