Computing CAGR on every field

Hi,

I hope someone can help. I want to compute the CAGR per year for the following fields: Region, Company, Country and Segment.

I want my based year in 2021 that I want to compute for 5 years.

Example: Beginning Value $100 (yr 2021) / End Value $150 (yr 2022)

Beginning Value $100 (yr 2021) / End Value $175 (yr 2023)

Beginning Value $100 (yr 2021) / End Value $225 (yr 2024) and so on...

I find it very challenging on how this process works. Please let me know if you need additional information.

Best regards,

Michael

TEST_Raw CAGR from PPTX.xlsx

Workflow

Tips and Tricks

Common Use Cases

Best Practices

Expression

Accepted answers

KilianL

Hi @Mwatts23 ,

that was a nice little challenge.

I used the CAGR formula from here: https://www.investopedia.com/terms/c/cagr.asp

Steps taken:

1. aggregate by field of interest, e.g. company

2. pick base value

3. add base value to table

4. get number of years (n)

5. calculate CAGR following the formula

I added an example how to do this dynamically for any number of fields, see attached.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

CAGR.yxmd

KilianL

Hi @Mwatts23 ,

to include values from other rows in your calculation, you can use the multi-row formula tool.

Given your examples, this might look like: ([Row-1:Profit] * [Row-1:CAGR]) + [Row-1:Profit]

All comments

KilianL

Hi @Mwatts23 ,

that was a nice little challenge.

I used the CAGR formula from here: https://www.investopedia.com/terms/c/cagr.asp

Steps taken:

1. aggregate by field of interest, e.g. company

2. pick base value

3. add base value to table

4. get number of years (n)

5. calculate CAGR following the formula

I added an example how to do this dynamically for any number of fields, see attached.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

CAGR.yxmd

Mwatts23

Hi @KilianL,

Thank you so much! this community is just amazing as always! the CAGR is what I expected to be.

Now, if I want to join it back to the main data, do I just need to transpose the Name and Value fields before joining?

This community is fun and full of mentors. Thank you @KilianL .

Best regards,

Michael

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703