Beastmode Count help
Hello, I'm looking to create a beast mode to provide a summary number but my case statement counts are not working. The data looks like this:
Message Key Data Source
1 Collected
Delivered
2 Collected
3 Collected
3 Collected
Delivered
Delivered never has a message key but collected could have duplicate message keys. I'd like to say Count distinct meassage key if data source = Collected and SUM that together with count data source if data source = Delivered. The total above would be 5( 2 delivered and 3 unique collected). But I've tried many case statements with sum, with count distinct, count, etc and when I put them in the summary number, it turns up blank. If I add it to a column in a table, the table gives me an error and says it can't be done. This isn't that hard but something is wrong with my data or my beast mode since it's not working. Thanks, Wendi
Best Answer
-
I think that you want to take the count function out of the case statements. Try something like this:
COUNT(DISTINCT CASE WHEN `Data Source`='Collected' THEN `Message Key` END)
+
COUNT(CASE WHEN `Data Source` = 'Delivered' THEN `Data Source` END)
0
Answers
-
I think the problem is adding the two cases together. This is what I came up with:
(CASE
when `Data Source` = 'Collected'
then COUNT(DISTINCT `Message Key`)
end)
+
(CASE
when `Data Source` = 'Delivered'
then COUNT(`Data Source`)
end)Both cases work fine separately, but when I add in the " + " operator, the results are blank.
0 -
Yep, that's what I got! Any ideas on how to do this without the case then or other thoughts on adding the cases together. Thanks, Wendi
0 -
I think that you want to take the count function out of the case statements. Try something like this:
COUNT(DISTINCT CASE WHEN `Data Source`='Collected' THEN `Message Key` END)
+
COUNT(CASE WHEN `Data Source` = 'Delivered' THEN `Data Source` END)
0 -
I thought I tried all possible combinations of count and sum but I guess not this one because this one worked!!! Thank you, Wendi
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 307 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 790 Beast Mode
- 78 App Studio
- 43 Variables
- 745 Automate
- 187 Apps
- 475 APIs & Domo Developer
- 67 Workflows
- 16 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 139 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive