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
