Dynamic CAGR formula?

I know how to do a basic CAGR formula between two static years:
power((sum(CASE WHEN Fiscal Year = 2024 THEN Amount else 0 end)
/ sum(case when Fiscal Year = 2020 then Amount end)), 1 / 4)
-1
Is there a way to construct a CAGR calculation that responds to user filtering or changing the timeframe? I am trying to think of a way to make it so a user can choose a different start and end year and have it calculate the CAGR correctly.
Best Answer
-
Hello @ccccc4,
It's a bit complicated and I'll try to explain in detail how I approached this. First, I've created a Variable for the Start Period as follows:
I applied the same process for the CAGR end period, defining the start and end years for the calculation. Using these variables, I calculated the CAGR with the following formula:
Power(
(SUM(CASE WHENParts Kits Fiscal Year
=SEG PKA CAGR End Period
THENParts Kits Qty
END)
/
SUM(CASE WHENParts Kits Fiscal Year
=SEG PKA CAGR Start Period
THENParts Kits Qty
END))
,1/(SEG PKA CAGR End Period
-SEG PKA CAGR Start Period
+1))-1On the dashboard, you can set the CAGR calculation period (I know, I also hate the decimal in the year).
This automatically calculates the CAGR for the 2019–2023 period in the following graph:
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
1
Answers
-
VARIABLES!!!
Beast of a topic, so I'll defer to Domo's existing knowledge base - see link below.
It's kind of a Beastmode for Beastmodes (BeastModes^2???)
This should get you were you want to be - let me know if I misunderstood
✅Did this solve your problem? Accept it as a solution!
❤️Did you love this answer? Mark it as "Awesome"!
👍Do you agree with this process? Click "Agree"!
0 -
Hello @ccccc4,
It's a bit complicated and I'll try to explain in detail how I approached this. First, I've created a Variable for the Start Period as follows:
I applied the same process for the CAGR end period, defining the start and end years for the calculation. Using these variables, I calculated the CAGR with the following formula:
Power(
(SUM(CASE WHENParts Kits Fiscal Year
=SEG PKA CAGR End Period
THENParts Kits Qty
END)
/
SUM(CASE WHENParts Kits Fiscal Year
=SEG PKA CAGR Start Period
THENParts Kits Qty
END))
,1/(SEG PKA CAGR End Period
-SEG PKA CAGR Start Period
+1))-1On the dashboard, you can set the CAGR calculation period (I know, I also hate the decimal in the year).
This automatically calculates the CAGR for the 2019–2023 period in the following graph:
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
1 -
Thanks! I was able to replicate this and it works great. It would be nice if Variables were more dynamic (i.e point the variable to an existing field and it populates the values. My data already has the Fiscal Year field in it, so having to manually add each year to the Variable selection list is tedious.
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive