Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

Expression for cal CGAR ?

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

20 Replies

Re: Expression for cal CGAR ?

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

paulyeo11
Valued Contributor II

Re: Expression for cal CGAR ?

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 ?

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?

paulyeo11
Valued Contributor II

Re: Expression for cal CGAR ?

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 ?

May be something like this:

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


Capture.PNG

Capture.PNG

paulyeo11
Valued Contributor II

Re: Expression for cal CGAR ?

Hi Sunny

Can you share with me FirstSortedValue how can get it ?

Paul

Sent from my iPhone

Re: Expression for cal CGAR ?

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

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

vikasmahajan
Honored Contributor III

Re: Expression for cal CGAR ?

prma7799
Honored Contributor III

Re: Expression for cal CGAR ?

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

Community Browser