Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
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
Thanks a lot Sunny Bhai.. Worked perfectly....
It did, but do you really understand what is going on? Are you interested in learning what is going on?
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..
Sunny Bhai,
Can you please tell me what is exactly happening with Numeric one..
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.
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..
Both your questions are answered in the post that I shared a link for. Sharing it again.... do read it through
Thank you Bhai...