3 Month Rolling

[bharatkishore]

Hi All,

stalwar1‌

I have a combo chart and Year and Month as dimensions.

I have written the following expression:

sum({<Month={'>=$(=max(Month)-2)<=$(=max(Month))'}>}aggr(rangesum(below(total sum({<Month=>}Consumption),0,3)),Month))

The expression is working fine but it is showing only value in chart. Attached image for reference.

Can you please tell me where i am doing wrong.

Thanks,

Bharat

[bharatkishore]

Thanks a lot Sunny Bhai.. Worked perfectly....

[sunny_talwar]

It did, but do you really understand what is going on? Are you interested in learning what is going on?

[bharatkishore]

Yes Sunny Bhai.. that is the next one i want to ask.. Just testing in final one..

Can you please tell me what does Numeric do..

[bharatkishore]

Sunny Bhai,

Can you please tell me what is exactly  happening with Numeric one..

[sunny_talwar]

Aggr() function by default sort your expression using the sort order of your field in the script.... When using RangeSum(Above()) function... it is important we have the correct sort to be able to get the correct 3 month aggregation... for example.... if you have the data coming in like this

Month     Amount

Jan          10

Oct           30

Nov          20

Feb          10

Jul            60

Mar          30

May         10

Apr          20

Jun          30

Sep          50

Dec          100

Aug          10

your 3 month accumulation will look like this

Month     Incorrect                          Correct

Jan          10 (10)                              10 (10)

Feb          60 (10 + 20 + 30)              20 (10 + 10)

Mar          100 (30 + 60 + 10)            50 (10 + 10 + 30)

and so on....

To fix this, you have two options

1) Fix the load order in the script on the field you are using in your Aggr() function - MonthYear field in your case

2) or use The sortable Aggr function is finally here! which is only available in QV12 or above (and also in most version of Qlik Sense).

Does this make sense now? I hope you will have a better grip of this when you run into a similar issue down the line.

[bharatkishore]

Thank you so much Sunny Bhai for an awesome example and the way you told it will be easily understood by any one. Thank you so much again and again..

One question why finally you have used Numeric ,will it give proper order for month year just a guess or if not this means can you tell me what does it do..

Why only in QV 12 or above i need to give this(Numeric). Because yesterday it was fine but today it didn't i mean after refresh..

[sunny_talwar]

Both your questions are answered in the post that I shared a link for. Sharing it again.... do read it through

The sortable Aggr function is finally here!

[bharatkishore]

Thank you Bhai...