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

Best Answer

  • ST_-Superman-_
    Answer ✓

    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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

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!**
  • ST_-Superman-_
    Answer ✓

    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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Manasi_Panov
    Manasi_Panov 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!