Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Computing CAGR on every field

Mwatts23
8 - Asteroid

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

6 REPLIES 6
KilianL
Alteryx Alumni (Retired)

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.

 

CAGR.png

 

 

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

 

 

 

Mwatts23
8 - Asteroid

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

KilianL
Alteryx Alumni (Retired)

Hi @Mwatts23 ,

 

joining it back to the main data does not work out, because I aggregated the data by field for the CAGR calculation. This was an assumption I made. What is the desired output you want to see?

 

I quickly added a calculation without any aggregation, but the data does not make sense to me in that case. Some years are missing and some values are duplicated.

For example:

AMER A Brazil Enterprise 2021 12
AMER A Brazil Enterprise 2021 13

Mwatts23
8 - Asteroid

Hi @KilianL ,

 

You are right. I just thought if there is a way to join it back to the main file 😅. Sorry, but I have another question in relation to this data.

Mwatts23_1-1653605582721.png

 

From the above screenshot, how can you insert a new profit for 2023, 2024 and 2025 for each region using this

calculation for example in AMER Region.

 

2023 profit:  (1,197 * 1.25) + 1197 = 2,693

2024 profit:  (2,693 * .87) + 2693 = 5,036

2025 profit:  (5,036 * .56) + 5036 = 7,856

 

 

Thank you for taking time on my queries.

 

Best regards,

Michael

KilianL
Alteryx Alumni (Retired)

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]

Mwatts23
8 - Asteroid

Thank you @KilianL  for this information.

Labels