Best mode percentage change incorrect value
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

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.1
Answers

Hi,
Is anyone able to help out with this request?
Thanks,0 
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,0
Categories
 11K All Categories
 5 Private Company Board
 2 APAC User Group
 12 Welcome
 42 Domo News
 9.9K Using Domo
 2K Dataflows
 2.5K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 349 Workbench
 267 Domo Best Practices
 12 Domo Certification
 474 Domo Developer
 51 Domo Everywhere
 110 Apps
 726 New to Domo
 85 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 29 お知らせ
 65 Kowaza
 302 仲間に相談
 664 ひらめき共有