Beast Mode Formula error

Manasi_Panov

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 QtyELSE 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

ST_Superman

I don't think beastmodes like the BETWEEN clause.

Instead of:

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

GrantSmith

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

ST_Superman

I don't think beastmodes like the BETWEEN clause.

Instead of:

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

Manasi_Panov

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