How do I compute a CAGR using Spotfire custom expressions

[Gabriel Rotily]

Hello,

If I want to calculate a CAGR for metric "A" between Year "X" and Year "Y", what should be my custom made expression on Spotfire TIBCOCloud

Many thanks in advance for any help on this.

Best,

Gabriel

[Gaia Paolini]

could you upload a sample of your data, and elaborate on how this expression would appear on the page Is it a new column or some calculated KPI

[Gabriel Rotily]

Hello,

The data looks as follows and I would like to calculate a CAGR for 2017-2019 directly on Spotfire.

Many thanks for your help !

Data:

 

 

 

Transaction ID

Sales

Year

 

 

1

118

2017

 

 

2

734

2017

 

 

3

285

2017

 

 

4

892

2018

 

 

5

558

2018

 

 

6

284

2018

 

 

7

794

2018

 

 

8

835

2019

 

 

9

970

2019

 

 

10

467

2019

[Gaia Paolini]

Ok, so using this formula https://www.investopedia.com/terms/c/cagr.asp for CAGR:

In a TextArea set up two integer document properties startYear and endYear which each can span the unique values of the [Year] column.

If you want to display it in a text area as a calculated value, I could not work out a solution without precomputing two calculated columns:

[startValue]:

If([Year]=${startYear},ValueForMin([Transaction ID],[sales]) over ([Year]),NULL)

[endValue]:

If([Year]=${endYear},ValueForMax([Transaction ID],[sales]) over ([Year]),NULL)

 

Then compute the calculated value as:

Power(Max([endvalue]) / Max([startvalue]),1/(${endYear}-${startYear}+1))-1

I added the +1 in the count of years as I suppose that if you do it for e.g. 2017 to 2019 you are actually counting 3 years not 2. But anyway you can use the formula you choose.

 

If you want to compute it as a calculated column in the data table, I could do it without the intermediate columns [startValue] and [endValue] as:

Power(Max(If([Year]=${endYear},ValueForMax([Transaction ID],[sales]) over ([Year]),NULL)) / Max(If([Year]=${startYear},ValueForMin([Transaction ID],[sales]) over ([Year]),NULL)),1/(${endYear}-${startYear}+1))-1