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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive