topic 12 rolling months in QlikView

12 rolling months

Anonymous — Wed, 30 Apr 2014 13:52:52 GMT

Hello community,

I'm facing an issue by trying to make a 12 rolling months in a line chart.

This chart has a calculation condition: GetSelectedCount(YEAR)=1 and GetSelectedCount(MONTH)=1

Here is my data model to make this chart :

NB is field which counts the number of months different of the period YYYYMM12RM. For example :

%YYYYMM YYYYMM12RM NB

201404 201404 1

201404 201403 2

201404 201402 3

201404 201401 4

201404 201312 5

201404 201311 6

201404 201310 7

201404 201309 8

201404 201308 9

201404 201307 10

201404 201306 11

201404 201305 12

I use in the dimensions the field %YYYYMM.

The expression used is: Sum({<YEAR=, MONTH=, %YYYYMM={">=$(vPeriodMin)<=$(vPeriodMax)"}>} Sales)

where vPeriodMin=Max({<NB={'12'}>} YYYYMM12RM) and vPeriodMax=Max({<NB={'1'}>} YYYYMM12RM).

It seems that my expression doesn't take into account my Set Analysis.

Does anyone have an idea please ?

Thanks in advance,

Regards,

Quentin

Re: 12 rolling months

mphekin12 — Wed, 30 Apr 2014 14:01:55 GMT

Quentin,

Please read this post by Gysbert Wassenaar:

Calculating rolling n-period totals, averages or other aggregations

Re: 12 rolling months

Anonymous — Wed, 30 Apr 2014 14:37:02 GMT

Thanks for the fast answer mphekin12.

I tried to understand Gysbert's example.

In his example, the table AsOfMonth looks like my table Calendar-1. If I understood well, I just needed to change the dimension in my chart as it is showed in Gybert's example but the sum are calculated only for some date.

Could you please help me to understand ?

Thanks in advance,

Regards,

Quentin

Re: 12 rolling months

Anonymous — Fri, 02 May 2014 12:51:26 GMT

Any ideas ?

Thanks in advance,

Quentin

Re: 12 rolling months

Anonymous — Fri, 02 May 2014 13:30:22 GMT

Hello,

I finally found a solution for my 12 rolling months line chart.

Here is my dimension:

=if(right(YYYYMM12RM, 2)='01',

if(YYYYMM12RM+11>=$(vPeriodMin) and YYYYMM12RM+11<=$(vPeriodMax),

YYYYMM12RM+11

if(YYYYMM12RM+99>=$(vPeriodMin) and YYYYMM12RM+99<=$(vPeriodMax),

YYYYMM12RM+99

)

Here is my expression:

Above(Sum({<YEAR=, MONTH=, %Date=, %YYYYMM={'>=$(vPeriodMin))<=$(vPeriodMax)'}>} [Sales Quantity MTH]), 0, 11)

Thanks to mphekin12 and Gysbert Wassenaar for their help.

Regards,

Quentin