Charting

Charting

Subtotals for Time on a Card

How do I get subtotals for time calculated by beast mode?

image.png

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • Coach
    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:

    1. SEC_TO_TIME(SUM(case
    2. when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp`
    3. else 0
    4. end))

Answers

  • @Goodeman Is your Total Unavailable Mins column already a beast mode? If so, could you please share your formula?

  • Member
    edited May 2023

    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

  • Also data is structured this way.

    image.png
  • @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?

  • Member
    edited May 2023

    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.

  • Coach
    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:

    1. SEC_TO_TIME(SUM(case
    2. when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp`
    3. else 0
    4. end))
  • Thanks,

    I will give this a shot.

  • Thank you MichelleH that worked like a charm!

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In