Beast Mode Formula error

Manasi_Panov
Manasi_Panov Contributor
in Beast Mode

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

If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Tagged:

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    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

Answers

  • GrantSmith
    GrantSmith Coach

    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
    ST_Superman Domo Employee
    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

  • Manasi_Panov
    Manasi_Panov Contributor
    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!

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories