Issue with Nested Case Statements
I have a dataset of call records. I am attempting to filter out records that fall outside of business hours when the 'Call Result' is 'No Agents in Queue,' based on the product the call was associated to. I have created the following Beast Mode calculation to do so:
CASE `Last Leg Product` WHEN 'NPAC-CCS' THEN (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END) ELSE (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END) END
There is a filter on the card called to show only cases where 'No Agents Business Hours Filter' = 0. All other products are correct, but for 'NPAC-CCS' calls there are still cases outside of the defined business hours.
Best Answer
-
Hi @MosesWynn
Are all of your NPAC-CCS records still appearing or are some records being filtered / identified correctly (Essentially - are you getting into the first section of your beast mode)? Is it possible there's whitespace around the `Last Leg Product` field (did you try and wrap it in a TRIM function)?
Have you tried rewriting your CASE to be CASE WHEN `Last Leg Product` = 'NPAC-CCS'
(they should be logically identical though)?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Hi @MosesWynn
Are all of your NPAC-CCS records still appearing or are some records being filtered / identified correctly (Essentially - are you getting into the first section of your beast mode)? Is it possible there's whitespace around the `Last Leg Product` field (did you try and wrap it in a TRIM function)?
Have you tried rewriting your CASE to be CASE WHEN `Last Leg Product` = 'NPAC-CCS'
(they should be logically identical though)?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hi @GrantSmith,
Thanks for your response.
'NPAC-CCS' records that fall outside of the span 8AM to 8PM are being filtered out, as per the ELSE clause. I reformatted to the following, but am still getting the same results.
CASE WHEN TRIM(`Last Leg Product`) = 'NPAC-CCS ' THEN (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END) ELSE (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END) END
Thanks!
Moses
0 -
Hi @MosesWynn
Did you mean to have the trailing space in 'NPAC-CCS '? Doing a TRIM on your column and comparing it to the value with a trailing space will never match up.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Actually, your suggestion worked. I'm realizing I accidentally had a space after CCS in my formula.
Thanks!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive