topic Year to Year Analysis in QlikView

Year to Year Analysis

Fri, 14 Oct 2011 16:57:30 GMT

i cannot find the formula for a year to year analysis

let say the following example:

YearQuarter A/R days

2009 44

2010 37

2011 42

I try to use the formula :

=Sum({$<Year={$(=Max(Year))}>}( [A/R days])) + (Sum({$<Year={$(=Max(Year)-1)}>} [A/R days]))

But then, i have the following table for the selection Q1-2011

2010 37

2011 42

now, can you tell me which formula i need to use so that the result will be

2010 ( 37/44)-1

2011 (42/37)-1

thanks in advance

Re: Year to Year Analysis

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 17:54:33 GMT

Hi,

Think of using Before() and After() functions in a pivot table or straight table. When Year is the dimension, sorted numeric ascending, and is pivoted to the top horizontal, and Sum([A/R days]) the aggregated value for each year, this should return as you expect

(Sum([A/R days]) / Before([A/R days])) -1

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Year to Year Analysis

Sat, 15 Oct 2011 06:36:33 GMT

Hi Miguel

unfortunately, this is not working ...