Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

vishalgoud
Contributor

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
Valued Contributor II

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

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

7 Replies
olivierrobin
Valued Contributor II

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

hello

you could refer to the post of henric cronstrom

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

vishalgoud
Contributor

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

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
Valued Contributor II

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

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
Contributor

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

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
Valued Contributor II

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

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
Contributor

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

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

MVP
MVP

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

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

Community Browser