Subtotals for Time on a Card

Goodeman
Goodeman Member
in Charting

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

image.png

Best Answer

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

    SEC_TO_TIME(SUM(case 
  when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp`
  else 0
  end))

Answers

  • MichelleH
    MichelleH Coach

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

  • Goodeman
    Goodeman 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

  • Goodeman
    Goodeman Member

    Also data is structured this way.

    image.png
  • MichelleH
    MichelleH Coach

    @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?

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

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

    SEC_TO_TIME(SUM(case 
  when `agentState` = 'Unavailable' then `eventTimestamp` - `previousEventTimestamp`
  else 0
  end))
  • Goodeman
    Goodeman Member

    Thanks,

    I will give this a shot.

  • Goodeman
    Goodeman Member

    Thank you MichelleH that worked like a charm!

Categories