How would I pull the highest rate over the last 12 months in a beastmode?
In an effort to create a comparitive gauge chart looking at the current month's rate compared to the avg monthly rate over the last 12 months, the highest rate over the last 12 months and the lowest rate over the last 12 months. This is the beastmode I'm currently working with to attemp to get the max rate:
MAX(SUM((COUNT(DISTINCT CASE WHEN Task.EventOutcome
= 'Converted' AND Created Date
>= DATE_SUB(CURDATE(), INTERVAL 365 DAY) THEN Task.ActivityID
END) OVER (PARTITION BY MONTH(Created Date
)))
/
(COUNT(DISTINCT CASE WHEN Created Date
>= DATE_SUB(CURDATE(),INTERVAL 365 DAY) THEN Task.ActivityID
END ) OVER (PARTITION BY MONTH(Created Date
)))))
Using this beast mode in a single value chart to see what it brings up, I'm finding that even though the beast mode editor has validated the beast mode, nothing is pulling up on the chart. Where am I going wrong here?
Answers
-
It's pretty difficult to tell where the error is here. I tend to try to break these complicated calculated fields up firts to make sure that I am getting the correct values for each "step". For example, make sure your numerator value is coming through correctly, then confirm the denominator value. Then put the whole thing together. That being said, you are doing a lot of aggregating here. Taking the Max of the Sum of the distinct count. Not sure how Domo will handle that. You could try this?
MAX(
SUM(
(COUNT(DISTINCT
CASE WHENTask.EventOutcome
= 'Converted' ANDCreated Date
>= DATE_SUB(CURDATE(), INTERVAL 365 DAY) THENTask.ActivityID
END))) OVER (PARTITION BY MONTH(`Created Date`)) / SUM(COUNT(DISTINCT
CASE WHENCreated Date
>= DATE_SUB(CURDATE(),INTERVAL 365 DAY) THENTask.ActivityID
END ))
OVER (PARTITION BY MONTH(`Created Date`))
)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
Thank you for the attempt! Domo is still not liking it. I'll probably have to create it in an ETL.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive