Year to Year Analysis

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

Hi Miguel

unfortunately, this is not working ...