Count how many Providers submit a range of cases
Hello, I am trying to segment our Providers by how many cases they submit.
I have the formula below but I have something wrong. Ideally it will tell me how many Providers submit between 1 and 10 cases and 11 to 20 cases labeled them as Provider 1-10. This count will change throughout the year as they continue to submit cases. When I know a case has been approved is then `FirstSetupApprovedDate` has a date, if it is blank I wouldnt want to count it
I hope someone can help
CASE
WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID`) >= -1 AND <= 10 then 'Povider 1-10'
WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID`) >= -11 AND <= 20 then 'Povider 11-20'
end
Best Answer
-
Your AND statement is slightly off. You are also missing an END statement inside the count. You have to re-state what you are evaluating, like this:
CASE WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -1 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 10 then 'Povider 1-10' WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -11 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 20 then 'Povider 11-20' end
You might also consider moving the case statement to the very outside, which might work better:
CASE when `FirstSetupApprovedDate` IS NOT NULL then (CASE WHEN COUNT(`CaseID`) >= -1 AND COUNT(`CaseID`) <= 10 then 'Povider 1-10' WHEN COUNT(`CaseID`) >= -11 AND COUNT(`CaseID`) <= 20 then 'Povider 11-20' end) END
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.3
Answers
-
Your AND statement is slightly off. You are also missing an END statement inside the count. You have to re-state what you are evaluating, like this:
CASE WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -1 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 10 then 'Povider 1-10' WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -11 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 20 then 'Povider 11-20' end
You might also consider moving the case statement to the very outside, which might work better:
CASE when `FirstSetupApprovedDate` IS NOT NULL then (CASE WHEN COUNT(`CaseID`) >= -1 AND COUNT(`CaseID`) <= 10 then 'Povider 1-10' WHEN COUNT(`CaseID`) >= -11 AND COUNT(`CaseID`) <= 20 then 'Povider 11-20' end) END
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
Thank you it worked. I am trying to get a vertical graph to split the by provider "level" I think the formula works but I am still working on my graph
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 792 Beast Mode
- 78 App Studio
- 44 Variables
- 754 Automate
- 187 Apps
- 479 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 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