Syntax Error in Beast mode calculation

Options
agolla2
agolla2 Member
in Beast Mode

CASE
WHEN Sort: Month Number = 1 THEN NULL
ELSE
(
(SUM(All Cases) - LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)) /
LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)
)
END

I'm not able to figure out what is the syntax error over here. Can anyone please help?

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    edited April 25 Answer ✓
    Options

    Colemen's answer will give an empty string instead of a NULL. If you want a NULL, then you could flip your CASE statement. Everything that is not "not 1" will be NULL:

    CASE
    WHEN Sort: Month Number != 1 THEN
    (
    (SUM(All Cases) - LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)) /
    LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)
    )
    END

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • ColemenWilson
    ColemenWilson Coach
    Options

    You can't do WHEN Sort: Month Number = 1 THEN NULL

    You could do WHEN Sort: Month Number = 1 THEN ''

    If I solved your problem, please select "yes" above

  • DavidChurchman
    DavidChurchman Coach
    edited April 25 Answer ✓
    Options

    Colemen's answer will give an empty string instead of a NULL. If you want a NULL, then you could flip your CASE statement. Everything that is not "not 1" will be NULL:

    CASE
    WHEN Sort: Month Number != 1 THEN
    (
    (SUM(All Cases) - LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)) /
    LAG(SUM(All Cases), 1) OVER (ORDER BY Sort: Month Number)
    )
    END

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories