Hello,
Can anyone please help me figure out why the following beast mode calculation returns the number of registrations in the previous month instead of a ratio ?
((
-- registrations this month
SUM(CASE WHEN MONTH(`date_joined`) = MONTH(CURRENT_DATE()) THEN 1 ELSE 0 END)
-
-- registrations previous month
SUM(CASE WHEN MONTH(`date_joined`) = MONTH(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 1 ELSE 0 END)
)
/
SUM(CASE WHEN MONTH(`date_joined`) = MONTH(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 1 ELSE 0 END))
It's virtually the same as the Variance % ((CY - PY) / PY) example from beast mode calculation examples and that example exhibits the same behaviour. What's interesting is that this:
((
-- registrations this month
SUM(CASE WHEN MONTH(`date_joined`) = MONTH(CURRENT_DATE()) THEN 1 ELSE 0 END)
-
-- registrations previous month
SUM(CASE WHEN MONTH(`date_joined`) = MONTH(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 1 ELSE 0 END)
)
return the correct absolute change MoM but when I try dividing by the previous months registrations again it breaks.