Custom Statistics CAGR

[#1]

Hello,

Im trying to build a custom statistic, what i'm trying to accomplish is to get the CAGR (compound annual growth rate) for a chart that i'm displaying. What i need to do is to get the value from the last year, the first year and the number of years in between. Then apply a mathematical formula and display a value.

I dont know if it is possible to do a custom statistic, or use a table, etc.

Thanks in advance!

[pover · Accepted solution]

From what I understand of the formula, you can make the following expression:

pow(sum({$<Year={$(=max(Year))}>} Revenue)/sum({$<Year={$(=min(Year))}>} Revenue),

1/(max(Year)-min(Year)))

That will work in a gauge chart or text object where the user selects a range of year.

Regards.

[#3]

Thank You!

It worked Great!, i dont have so much experience with dynamic variables or referred, i dont know which one is the right term.

Just if anyone else read this, you can add

- 1

at the end to the the CAGR formula complete

[#4]

Hi!

I am so happy to find sth related CAGR calculation and I was applying above logic as follows:

=pow(sum({$<Year={$(=max(Year))}>} sales)/sum({$<Year={$(=min(Year))}>} sales), 1(max(year)-min(year)))-1

my data table is:

2007     2008     2008     2010     2011

45          49          42          46          48

when I calculate this in excel =(48/45)^(1/4)-1 I get 1.6891 % but I don´t get the same result in QlikView.

Any ideas what I am doing wrong??

Thanks a lot!

Adi

[swuehl]

Maybe it's just a copy&paste issue, but aren't you missing a '/' here?

1 / (max(year)-min(year))

What result do you get?

[#6]

It is a copy paste issue... this is the formula I am using:

=pow(sum({$<Year={$(=max(Year))}>} sales)/sum({$<Year={$(=min(Year))}>}

sales),1/(max(Year)-min(Year)))-1

The result I get is 0.0170208...

[swuehl]

If I calculate the CAGR using your sample data in a calculator or excel, I do get 1.63 %, not 1.6891 %, but that won't resolve your QV problem.

Have you checked each partial expression (i.e. each sum() / max() / min() function) separately that these meet the expected outcome? Maybe it's a data issue.

[#8]

It was indeed a data issue and I was able to solve it.

Thanks!

[Anonymous]

Hi, quick question on cagr - if I wanted to build a line chart that shows CAGR % over the periods that user selects. How can I achieve that? Please advise, thanks!

[Anonymous]

Hi, I am trying to do the same thing but by qtr and/ or years, and apply that on a line chart, would this formula still work?

Thanks