topic Custom Statistics CAGR in QlikView

Custom Statistics CAGR

Mon, 27 Sep 2010 23:30:31 GMT

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!

Custom Statistics CAGR

pover — Tue, 28 Sep 2010 17:39:23 GMT

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.

Custom Statistics CAGR

Tue, 28 Sep 2010 18:07:41 GMT

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

Custom Statistics CAGR

Tue, 28 Feb 2012 15:51:41 GMT

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

Custom Statistics CAGR

swuehl — Tue, 28 Feb 2012 16:01:47 GMT

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?

Re: Custom Statistics CAGR

Tue, 28 Feb 2012 16:30:02 GMT

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...

Custom Statistics CAGR

swuehl — Tue, 28 Feb 2012 16:42:55 GMT

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.

Custom Statistics CAGR

Wed, 29 Feb 2012 02:39:04 GMT

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

Thanks!

Re: Custom Statistics CAGR

Anonymous — Tue, 15 Jul 2014 03:31:20 GMT

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!

Re: Custom Statistics CAGR

Anonymous — Fri, 18 Jul 2014 02:08:14 GMT

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

Re: Custom Statistics CAGR

CBRZ — Tue, 12 Mar 2019 14:51:07 GMT

Thanks for this, works great!

If I were to put set analysis within this CAGR, where would I put it?

I'm trying to make [Version] = 'Budget'

Thanks for your help!