topic Accumulated value of the last 12 months and of the previous period in QlikView

Accumulated value of the last 12 months and of the previous period

kira_whopper — Fri, 11 Mar 2016 12:10:27 GMT

Hi there,

I'm willing to calculate the value accumulated of the last 12 months for the 13 months displayed in a chart, for example:

Apr-2015 -> Sums from Apr-2015 until May-2014

Mar-2015 -> Sums from Mar-2015 until Apr-2014

Apr-2014 -> Sums from Apr-2015 until May-2013

Ok, that I got figured out with the following formula:

if(Only({<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"},

Year=,

Month=>} YearMonth),

RangeSum

(

Above

(

Sum( {<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -23), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"},

Year= ,

Month= >}

Value)

,0,12)

)

RangeSum

(

Above

(

Sum({<

YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},

Year=,

Month=>}

Value)

,0,12)

)

The Only within the IF condition, is for the calculation of the months outside the period of 13 months.

This works like a charm, but my problem now is to calculate the previous period, wich would be, in the same example:

Apr-2014 -> Sums from Apr-2014 until May-2013

Mar-2014 -> Sums from Mar-2014 until Apr-2013

Apr-2013 -> Sums from Apr-2013 until May-2012

I tryed using a AddYears(-1) in the YearMonth set analysis, but it didn't worked.

Any ideas?

Re: Accumulated value of the last 12 months and of the previous period

Gysbert_Wassenaar — Fri, 11 Mar 2016 13:41:14 GMT

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

Re: Accumulated value of the last 12 months and of the previous period

kira_whopper — Fri, 11 Mar 2016 13:46:50 GMT

A managed to do it without changing the script.

I modified the Abobe (from 0,12 to 12,12) and the YearMonth shift (from -23 and -12 to -35 and -24).

This is the new formula:

if(Only({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"}, Year=, Month=>} YearMonth),

RangeSum

(

Above

(

Sum( {<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -35), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1), 'MMM-YY'))"},

Year= ,

Month= >}

Value)

,12,12)

)

RangeSum

(

Above

(

Sum({<

YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -24), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},

Year=,

Month=>}

Value)

,12,12)

)