# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Creator

## 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({<

Year=,

Month=>} YearMonth),

RangeSum

(

Above

(

Sum( {<

Year= ,

Month= >}

Value)

,0,12)

)

,

RangeSum

(

Above

(

Sum({<

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
Creator
Author

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:

RangeSum

(

Above

(

Sum( {<

Year= ,

Month= >}

Value)

,12,12)

)

,

RangeSum

(

Above

(

Sum({<

Year=,

Month=>}

Value)

,12,12)

)

)

2 Replies

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

talk is cheap, supply exceeds demand
Creator
Author

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:

RangeSum

(

Above

(

Sum( {<

Year= ,

Month= >}

Value)

,12,12)

)

,

RangeSum

(

Above

(

Sum({<