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
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 668 Datasets
- Visualize & Apps
- 90 App Studio
- 199 Pro-code Components
- 2.6K Charting & Analyzer
- 874 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 285 Reporting
- Manage
- 146 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive