Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
kira_whopper
Contributor

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

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?




1 Solution

Accepted Solutions
kira_whopper
Contributor

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

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)

)

)

2 Replies
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
kira_whopper
Contributor

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

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)

)

)