Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
have to show rolling 12 months sales for the below month field. can any one help me with this exp.
when i select Month it has to show previous 12 months data in the bar chart. Thanks much
Month | Sales |
2011M01 | 4 |
2011M02 | 5 |
2011M03 | 6 |
2011M04 | 7 |
2011M05 | 8 |
2011M06 | 9 |
2011M07 | 10 |
2011M08 | 11 |
2011M09 | 12 |
2011M10 | 13 |
2011M11 | 14 |
2011M12 | 15 |
2012M01 | 16 |
2012M02 | 17 |
2012M03 | 18 |
2012M04 | 19 |
2012M05 | 20 |
2012M06 | 21 |
2012M07 | 22 |
2012M08 | 23 |
2012M09 | 24 |
2012M10 | 25 |
2012M11 | 26 |
2012M12 | 27 |
Hi, try with :
=if(GetSelectedCount(MONTH)>0, sum({<MONTH={">=$(=Date(AddMonths(MonthStart(Date#(MONTH,'YYYYMM')),-12),'YYYYMM')) <=$(=Date(MonthStart(Date#(MONTH,'YYYYMM')),'YYYYMM'))"}>} Sales), sum(Sales))
hello
you could refer to the post of henric cronstrom
https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table
Thanks for the reply Olivier,
But here my Month is in different format, was tried to bring it to normal format with below
purgechar(Month,'M') as MONTH,
and in the expression am using
= Sum({<MONTH ={"$(='>=' & Date(AddMonths(Max(MONTH),-12)) & '<=' & Date(Max(MONTH)))"}>}Sales)..
but with above exp am getting all previous all months, but not exactly the previous 12 months...
can you please help me in correct the exp to get previous 12 months please... Thanks in advance
Hi,
-in your load statement use as you described : purgechar(Month,'M') as MONTH,
and as expression :
=sum({<MONTH={">=$(=Date(AddMonths(MonthStart(Date#(MONTH,'YYYYMM')),-12),'YYYYMM')) <=$(=Date(MonthStart(Date#(MONTH,'YYYYMM')),'YYYYMM'))"}>} Sales)
thanks Jean, am getting the last 12 months data when i select any one of the month.
but when i didnt select any month -- we have to show all months data right ? but which is not coming with above expression..
Hi, try with :
=if(GetSelectedCount(MONTH)>0, sum({<MONTH={">=$(=Date(AddMonths(MonthStart(Date#(MONTH,'YYYYMM')),-12),'YYYYMM')) <=$(=Date(MonthStart(Date#(MONTH,'YYYYMM')),'YYYYMM'))"}>} Sales), sum(Sales))
Thats nice i tried same yesterday and it was working, and today you suggested the same. so am right. Thanks Much.
You can simplify a bit by removing the redundant addmonths() and using the parameter in monthend(), like:
=if(GetSelectedCount(MONTH)>0, sum({<MONTH={">=$(=Date(MonthStart(Date#(MONTH,'YYYYMM'),-12),'YYYYMM')) <=$(=Date(MonthStart(Date#(MONTH,'YYYYMM')),'YYYYMM'))"}>} Sales), sum(Sales))