Beast Mode Formula error

Member

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

Tagged:

• Coach

I don't think beastmodes like the BETWEEN clause.

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

Try this:

SUM(CASE WHEN `Instrument Fiscal Year` >= `SEG PMPK CAGR Start Period` AND  `Instrument Fiscal Year` < =`SEG PMKA CAGR End Period` THEN `Instrument 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.” -Superman

• Coach

Are you experiencing a syntax error or a logical error where you're not getting the results you're expecting?

**Did this solve your problem? Accept it as a solution!**
• Coach

I don't think beastmodes like the BETWEEN clause.

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

Try this:

SUM(CASE WHEN `Instrument Fiscal Year` >= `SEG PMPK CAGR Start Period` AND  `Instrument Fiscal Year` < =`SEG PMKA CAGR End Period` THEN `Instrument 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.” -Superman
• Member
edited August 2023

@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!