Aggregate (SUM) the results of Lead Function Beast mode at Card Level
I have a dataset wherein I have to count the number of cases which falls in the below filter and conditions.
CASE
-- Check if the current row is 'Ringing' and lasted 11 seconds or less
WHEN STATE
= 'Ringing'
AND TIME_TO_SEC(AGENT STATE TIME
) <= 11
AND LEAD(STATE
) OVER (PARTITION BY AGENT NAME
ORDER BY DATE
, AGENT NAME
) = 'Not Ready'
THEN 1
END
This Beast mode is giving me a count of all cases where an agent's phone rings for 11 seconds or less and is followed by a "Not Ready" state.
So, I have marked all of these cases with '1' beside my data as adding a new column. But when I try to sum these by physician, I'm not able to do it. I have even tried using Dataset view and grouping this by summing my beast mode column and Date, Agent name. But ended up getting an error message saying could not process this. All I'm trying to do is sum up these 1's and know for each agent how many of these cases have occured.
Thanks in Advance for the help.
Answers
-
SUM(CASE
WHEN STATE = 'Ringing'
AND TIME_TO_SEC(AGENT_STATE_TIME) <= 11
AND LEAD(STATE) OVER (PARTITION BY AGENT_NAME ORDER BY DATE, AGENT_NAME) = 'Not Ready'
THEN 1
ELSE 0
END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Thanks for the response. But getting 'This calculation contained an error' when using below formula.
SUM(CASE
WHENSTATE
= 'Ringing'
AND TIME_TO_SEC(AGENT STATE TIME
) <= 11
AND LEAD(STATE
) OVER (PARTITION BYAGENT NAME
ORDER BYDATE
,AGENT NAME
) = 'Not Ready'
THEN 1
ELSE 0
END)0 -
Make sure you have back ticks on your fields, and that your field names match the correct spelling.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive