Subtotals for Time on a Card

Goodeman

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

MichelleH

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

MichelleH

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

Goodeman

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

Also data is structured this way.

MichelleH

@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

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

@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

Thanks,

I will give this a shot.

Goodeman

Thank you MichelleH that worked like a charm!