Beast Mode Formula error
Could you please help me identify an error in my formula:
POWER((SUM(CASE WHEN Parts Kits Fiscal Year
=SEG PMKA CAGR End Period
THEN Parts Kits Qty
END)
/
SUM(CASE WHEN Instrument Fiscal Year
BETWEEN SEG PMPK CAGR Start Period
AND SEG PMKA CAGR End Period
THEN Instrument Qty
END))/(SUM(CASE WHEN Parts Kits Fiscal Year
=SEG PMPK CAGR Start Period
THEN Parts Kits Qty
END)
/
SUM(CASE WHEN Instrument Fiscal Year
BETWEEN SEG PMPK CAGR Start Period
AND SEG PMKA CAGR End Period
THEN Instrument Qty
END)),1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1
SEG PMPK CAGR Start Period
and SEG PMKA CAGR End Period
are numerical variables representing years. I aim to compute the CAGR for the selected period. This involves determining the ratio between the quantity of Parts Kits at the End Period year and the cumulative quantity of Instruments from the Start Period to the End Period.
I formulated the above equation using the two below-mentioned formulas, both of which are working fine.
CAGR calculation not for the ratio but the Qty:
Power(
(SUM(CASE WHEN Parts Kits Fiscal Year
=SEG PMKA CAGR End Period
THEN Parts Kits Qty
ELSE 0 END)
/
SUM(CASE WHEN Parts Kits Fiscal Year
=SEG PMPK CAGR Start Period
THEN Parts Kits Qty
ELSE 0 END))
,1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1
CAGR calculation of the ratio but with a predefined (fixed) period:
POWER((SUM(CASE WHEN Parts Kits Fiscal Year
=2023 THEN Parts Kits Qty
END)
/
SUM(CASE WHEN Instrument Fiscal Year
IN (2019,2020,2021,2022,2023) THEN Instrument Qty
END))/(SUM(CASE WHEN Parts Kits Fiscal Year
=2019 THEN Parts Kits Qty
END)
/
SUM(CASE WHEN Instrument Fiscal Year
IN (2015,2016,2017,2018,2019) THEN Instrument Qty
END)),1/5)-1
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
Best Answer
-
I don't think beastmodes like the BETWEEN clause.
Instead of:
SUM(CASE WHEN
Instrument Fiscal Year
BETWEENSEG PMPK CAGR Start Period
ANDSEG PMKA CAGR End Period
THENInstrument Qty
END)),1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1Try this:
SUM(CASE WHEN
Instrument Fiscal Year
>=SEG PMPK CAGR Start Period
ANDInstrument Fiscal Year
< =SEG PMKA CAGR End Period
THENInstrument Qty
END)),1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Answers
-
Are you experiencing a syntax error or a logical error where you're not getting the results you're expecting?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I don't think beastmodes like the BETWEEN clause.
Instead of:
SUM(CASE WHEN
Instrument Fiscal Year
BETWEENSEG PMPK CAGR Start Period
ANDSEG PMKA CAGR End Period
THENInstrument Qty
END)),1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1Try this:
SUM(CASE WHEN
Instrument Fiscal Year
>=SEG PMPK CAGR Start Period
ANDInstrument Fiscal Year
< =SEG PMKA CAGR End Period
THENInstrument Qty
END)),1/(SEG PMKA CAGR End Period
-SEG PMPK CAGR Start Period
+1))-1
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
@GrantSmith
At the beginning, there was a syntax mistake. I attempted the suggestion provided by @ST_-Superman-_ which resolved the syntax error and the formula is working. Thanks!If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive