Beastformula

I have this data with an ID column that are repeated because each unique id have 4 different event type, What I am trying to achieve is if one id with = 4 different category and are all completed in the event status column and within 45days of event date and originalenrollment date then it is completed.
I have this code below but seems not be capturing the statement above,
CASE
WHEN COUNT(DISTINCT ChildPlusID
) =Event Type
IN ('Hearing', 'Vision', 'ASQ3') AND Event Status Code
= 'Completed' AND DATEDIFF(Event Date
, OriginalEnrollmentDate
) <= 45 THEN 'Completed' END
Please help,
Thank you.
Best Answer
-
CASE WHEN COUNT(CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') THEN `ChildPlusID` END) = COUNT( CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') AND `Event Status Code` = 'Completed' AND DATEDIFF(`Event Date`, `OriginalEnrollmentDate`) <= 45 THEN `ChildPlusID` END) THEN 'Completed' END
This is assuming there are 4 records in your dataset with each of the event types. It will count the number of events found in that list and then compare it to the count of the number int the list which are also within 45 days.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
CASE WHEN COUNT(CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') THEN `ChildPlusID` END) = COUNT( CASE WHEN `Event Type` IN ('Hearing', 'Vision', 'ASQ3') AND `Event Status Code` = 'Completed' AND DATEDIFF(`Event Date`, `OriginalEnrollmentDate`) <= 45 THEN `ChildPlusID` END) THEN 'Completed' END
This is assuming there are 4 records in your dataset with each of the event types. It will count the number of events found in that list and then compare it to the count of the number int the list which are also within 45 days.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thank you GrantSmith.
CASE
WHEN
COUNT(
CASE
WHEN
`Event
Type
`
IN
('Hearing', 'Vision', 'ASQ3')
THEN
`ChildPlusID`
END
) = COUNT(
CASE
WHEN
`Event
Type
`
IN
('Hearing', 'Vision', 'ASQ3')
This part is giving syntax error
0 -
Can you post a screenshot of your code and the error your getting?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive