1 Reply Latest reply on May 19, 2015 1:16 PM by Katie Athaide

    Calculate growth rate using calculated field and display next to sales data

    Duc Pham



      I read about how to calculate CAGR here:



      My dataset has sales volume by years. I want to calculate the compounded growth rate between the last five years. I wrote the following formula:

      (lookup(sum([Off-trade Volume (mn litres)]),last())/lookup(sum([Off-trade Volume (mn litres)]),last()-5))^(1/total(count([Year])-1))-1


      How can I test if this formula returns correctly or not?


      Now, I would like to display for ONE company (using filters), for each geography that the company is in, what the latest yearly sales figure is, and what the CAGR has been for the last 5 years, both as texts. Then I want to repeat this, but with the CAGR as a color or size. However, I want to know: am I forced to display all of the years' sales volume in order for the calculated formula to work? Or can I just display the last year's sales figure and the 5-year CAGR will still display correctly?