Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalgoud
Creator III
Creator III

how to write rolling 12 month exp for this type of Month field??

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

   

MonthSales
2011M014
2011M025
2011M036
2011M047
2011M058
2011M069
2011M0710
2011M0811
2011M0912
2011M1013
2011M1114
2011M1215
2012M0116
2012M0217
2012M0318
2012M0419
2012M0520
2012M0621
2012M0722
2012M0823
2012M0924
2012M1025
2012M1126
2012M1227
1 Solution

Accepted Solutions
sergio0592
Specialist III
Specialist III

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

View solution in original post

7 Replies
olivierrobin
Specialist III
Specialist III

hello

you could refer to the post of henric cronstrom

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table

vishalgoud
Creator III
Creator III
Author

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

sergio0592
Specialist III
Specialist III

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)

vishalgoud
Creator III
Creator III
Author

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

sergio0592
Specialist III
Specialist III

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

vishalgoud
Creator III
Creator III
Author

Thats nice i tried same yesterday and it was working, and today you suggested the same. so am right. Thanks Much.

tresesco
MVP
MVP

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