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.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