Syntax Error in Beast mode calculation

agolla2

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?

DavidChurchman

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

ColemenWilson

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

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

DavidChurchman

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