Subtotals for Time on a Card
Best Answer
-
@Goodeman I see. Since you want to include a SUM and LEAD in the same calculation, I'd suggest adding the previous timestamp as a column in your dataflow instead of in the beast mode. If you are using MagicETL you can accomplish this using the a LEAD function of eventTimestamp in the Rank & Window tile, ordered by eventTimestamp and partitioned by agentState. Then your beast mode would look like this:
SEC_TO_TIME(SUM(case when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp` else 0 end))
0
Answers
-
Thanks for the fast response MichelleH
CASE
WHEN
agentState
= 'Unavailable' THEN SEC_TO_TIME(IFNULL((LEAD(UNIX_TIMESTAMP(eventTimestamp
)) OVER (ORDER BY (agent_lastName
))) - (UNIX_TIMESTAMP(eventTimestamp
)),0))End
0 -
Also data is structured this way.
0 -
@Goodeman It looks like your total is not populating because you are using a window function without an aggregation function (i.e. you need to include a SUM somewhere in the formula). I would also recommend moving the SEC_TO_TIME function to the outermost position in your formula. Can you share a little bit more about what you want the formula to accomplish?
0 -
Thanks,
(See data structure above). So the formula looks in the agent state column for "unavailable" and then takes the timestamp from the proceeding row to get the time difference between unavailable and whatever state the proceeding row has. It then orders it by the agent's last name. Then I want the card to list all "unavailable" times and then sum the times in subtotals.
I also failed to mention this is a pivot table card.
0 -
@Goodeman I see. Since you want to include a SUM and LEAD in the same calculation, I'd suggest adding the previous timestamp as a column in your dataflow instead of in the beast mode. If you are using MagicETL you can accomplish this using the a LEAD function of eventTimestamp in the Rank & Window tile, ordered by eventTimestamp and partitioned by agentState. Then your beast mode would look like this:
SEC_TO_TIME(SUM(case when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp` else 0 end))
0 -
Thanks,
I will give this a shot.
0 -
Thank you MichelleH that worked like a charm!
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 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