Rolling 12 Months Issue

[Rehan]

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

[Oleg_Troyansky]

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

[Rehan]

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

[Oleg_Troyansky]

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.

[pradosh_thakur]

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