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
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 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