topic Re: Expression for cal CGAR ? in QlikView

Expression for cal CGAR ?

paulyeo11 — Sun, 08 Nov 2015 03:44:59 GMT

HI All

i have attend the meeting last 20 year , I keep see people present their sales growth over the year . assume that company operate for last 10 year , and first sales 1 million and 2nd year 2 million and 10 year later this year in 2015 10 million sales.

may I know the expression for CGAR ?

Paul

‌

Re: Expression for cal CGAR ?

sunny_talwar — Sun, 08 Nov 2015 03:48:25 GMT

So are you looking to get Year-over-Year sales growth?

Re: Expression for cal CGAR ?

paulyeo11 — Sun, 08 Nov 2015 03:54:45 GMT

Hi Sunny

http://www.investopedia.com/terms/c/cagr.asp

I am looking for compound annual sales growth rates .

My question is how to create formula , so that I don't need maintain .

Paul

Sent from my iPhone

Re: Expression for cal CGAR ?

sunny_talwar — Sun, 08 Nov 2015 03:56:56 GMT

I know what it means, I am just trying to understand if you are looking to calculate this in the back end or front end of the script? and if you are trying to find the CAGR from min year to max year?

Re: Expression for cal CGAR ?

paulyeo11 — Sun, 08 Nov 2015 04:01:21 GMT

Hi Sunny

I need the expression at front end. I want to compare sales growth by company.

Paul

Sent from my iPhone

Re: Expression for cal CGAR ?

sunny_talwar — Sun, 08 Nov 2015 04:06:08 GMT

May be something like this:

=Num(exp((1/Count(Year)) * log(FirstSortedValue(Sales, -Year)/FirstSortedValue(Sales, Year))) - 1, '#,##0.00%')

Re: Expression for cal CGAR ?

paulyeo11 — Sun, 08 Nov 2015 11:34:08 GMT

Hi Sunny

Can you share with me FirstSortedValue how can get it ?

Paul

Sent from my iPhone

Re: Expression for cal CGAR ?

sunny_talwar — Sun, 08 Nov 2015 12:33:38 GMT

Paul, do you wish to know how FirstSortedValue() function work? Have you seen QlikView or Qlik Sense help?

Check out these links:

https://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/ChartFunctions/BasicAggregationFunctions/firstso…

Value Associated with Min/Max Value of Another Field (Front End Solution)

Re: Expression for cal CGAR ?

vikasmahajan — Mon, 09 Nov 2015 12:00:06 GMT

go through this https://community.qlik.com/message/821942#821942

Vikas

Re: Expression for cal CGAR ?

prma7799 — Mon, 09 Nov 2015 12:04:55 GMT

Try this

=((pow((((SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=
>}[Sales]))/100000)
/
((SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear-4))},MonthName=,Quarter=
>}[Sales]))/100000)),(1/4))-1)*100)

This is for five years CAGR

Re: Expression for cal CGAR ?

paulyeo11 — Mon, 09 Nov 2015 14:39:53 GMT

Hi Sunny

Thank you for share with me the link , i notice that you are the one who wrote the document. I am still not able to apply you expression , because my data structure are different from your sample QVW.

Paul

Re: Expression for cal CGAR ?

sunny_talwar — Mon, 09 Nov 2015 14:42:29 GMT

Can you throw in some sample data which shows how your data is structured?

Re: Expression for cal CGAR ?

paulyeo11 — Mon, 09 Nov 2015 14:46:20 GMT

Hi Sunny

I have attach my QV doc when i reply QlikView 7799 post.

Paul

Re: Expression for cal CGAR ?

sunny_talwar — Mon, 09 Nov 2015 14:51:12 GMT

Oh right, did not look at that. Will check it out now

Re: Expression for cal CGAR ?

paulyeo11 — Tue, 10 Nov 2015 03:27:39 GMT

Hi All

The QVW file last night i upload is very complicated , This time i try to upload the simple one.

Hope some one can advise me how to get the value of CAGR for 2010 till 2015.

Paul

Re: Expression for cal CGAR ?

prma7799 — Tue, 10 Nov 2015 04:26:41 GMT

Hi Paul,

Try to convert my fields with your existing fields.

Here I am not able to open your QVW.

Thanks

Re: Expression for cal CGAR ?

paulyeo11 — Tue, 10 Nov 2015 04:39:36 GMT

Hi QV 9977

I just upload a new QV doc ver 007

Can you pls take a look sure can open

Paul

Sent from my iPhone

Re: Expression for cal CGAR ?

prma7799 — Tue, 10 Nov 2015 05:00:47 GMT

Hi Paul,

Please try this

=((pow((((SUM({$<year=,year={$(=max(year))}

>}[sales]))/100000)

((SUM({$< year=,year={$(=max(year-4))}

>}[sales]))/100000)),(1/4))-1)*100)

I thing you have to create master calendar in your data model in proper way...

Re: Expression for cal CGAR ?

paulyeo11 — Tue, 10 Nov 2015 05:21:14 GMT

Hi QV7799

Thank you for your sharing.

I have apply your expression into my table , i found that the result is wrong.

Base on cal is 18% but your display -219%

Paul

Re: Expression for cal CGAR ?

prma7799 — Tue, 10 Nov 2015 05:29:17 GMT

Can you please send your expected output

please