Best mode percentage change incorrect value

kgr
kgr Member
in Magic ETL

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.


 

 

Best Answer

  • kgr
    kgr Member
    Answer ✓

    Thanks for your reply rahul93.

    It turns out that the problem was slightly different and I was able to pinpoint it with the help of Domo support. The thing was that I was displaying the number as a value in a Single Value card. That card only displays the first value in the dataset and I was using a series of timestamps as my "X".

    So the calculation itself was correct but I needed to change my "X" for it to display correctly. Using a bestmode like that:

    1

    works well in that case (as suggestd by support).

    Also when I used that calculation as a summary number (which actually excpects an aggregation) it worked fine.


Answers

  • laura_igl
    laura_igl Member

    Hi,

    Is anyone able to help out with this request?


    Thanks,

  • rahul93
    rahul93 Contributor

    If youre doing the same thing as the variance calculation that you have in your comment. I think you are doing the right thing. Maybe coincidentally it is a whole number and is the same as last years registrations. 

    I cannot say anything about that as I dont have any insight into the data. 

    Thanks,

  • kgr
    kgr Member
    Answer ✓

    Thanks for your reply rahul93.

    It turns out that the problem was slightly different and I was able to pinpoint it with the help of Domo support. The thing was that I was displaying the number as a value in a Single Value card. That card only displays the first value in the dataset and I was using a series of timestamps as my "X".

    So the calculation itself was correct but I needed to change my "X" for it to display correctly. Using a bestmode like that:

    1

    works well in that case (as suggestd by support).

    Also when I used that calculation as a summary number (which actually excpects an aggregation) it worked fine.


Categories