topic Re: 24 rolling Months from YYYYMM format in QlikView

24 rolling Months from YYYYMM format

jaymerry — Fri, 18 Oct 2013 15:21:14 GMT

Hello all,

I'm trying to calculate directly and dynamically into a chart the 24 rolling Months.

My time dimension has the following format : YYYYMM.

Have you ever calculated this kind of dimension ?

I tried to use the "Makedate" function, without success.

Thank you for your help,

Best Regards,

Jaymerry

Re: 24 rolling Months from YYYYMM format

jagan — Fri, 18 Oct 2013 15:33:25 GMT

Hi,

If you have date field in your datamodel, then use the following expression

=Sum({<YearDimension=, MonthDimension=, QuarterDimension=, DateDimension={'>=$(=MonthStart(Max(DateDimension), -23))<=$(=MonthEnd(Max(DateDimension)))'}>} MeasureName)

Exclude Year, Month, Quarter dimensions in the expression as in the above expression.

Hope it helps you.

Regards,

Jagan.

Re: 24 rolling Months from YYYYMM format

jagan — Fri, 18 Oct 2013 15:35:19 GMT

Hi,

You can arrive a date by using MakeDate() as below

=MakeDate(Left(DateDimension, 4), Num(Right(DateDimension, 2)), 1)

Regards,

Jagan.

Re: 24 rolling Months from YYYYMM format

jaymerry — Fri, 18 Oct 2013 15:47:34 GMT

Thank you Jagan,

I have found the following way, which works in my chart :

YEAR(addmonths(Makedate(left(%YYYYMM,4),right(%YYYYMM,2)),-24,1))&right(left(addmonths(Makedate(left(%YYYYMM,4),right(%YYYYMM,2)),-24,1),5),2)

Best Regards,

Jaymerry

Re: 24 rolling Months from YYYYMM format

Gysbert_Wassenaar — Fri, 18 Oct 2013 15:50:19 GMT

Read this document: Calculating rolling n-period totals, averages or other aggregations