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 -
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
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 618 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 741 Beast Mode
- 58 App Studio
- 40 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive