Aggregate (SUM) the results of Lead Function Beast mode at Card Level

agolla2

I have a dataset wherein I have to count the number of cases which falls in the below filter and conditions.

CASE
-- Check if the current row is 'Ringing' and lasted 11 seconds or less
WHEN STATE = 'Ringing'
AND TIME_TO_SEC(AGENT STATE TIME) <= 11
AND LEAD(STATE) OVER (PARTITION BY AGENT NAME ORDER BY DATE, AGENT NAME) = 'Not Ready'
THEN 1
END

This Beast mode is giving me a count of all cases where an agent's phone rings for 11 seconds or less and is followed by a "Not Ready" state.

So, I have marked all of these cases with '1' beside my data as adding a new column. But when I try to sum these by physician, I'm not able to do it. I have even tried using Dataset view and grouping this by summing my beast mode column and Date, Agent name. But ended up getting an error message saying could not process this. All I'm trying to do is sum up these 1's and know for each agent how many of these cases have occured.

Thanks in Advance for the help.

MichelleH

@agolla2 Beast modes don't typically work well with aggregating aggregate/window functions. I'd suggest calculating the LEAD function in an ETL so that you don't have to do it in beast mode

ArborRose

Make sure you have back ticks on your fields, and that your field names match the correct spelling.

ArborRose

SUM(CASE

    WHEN STATE = 'Ringing'

    AND TIME_TO_SEC(AGENT_STATE_TIME) <= 11

    AND LEAD(STATE) OVER (PARTITION BY AGENT_NAME ORDER BY DATE, AGENT_NAME) = 'Not Ready'

    THEN 1

    ELSE 0

END)

agolla2

Thanks for the response. But getting 'This calculation contained an error' when using below formula.

SUM(CASE
WHEN STATE = 'Ringing'
AND TIME_TO_SEC(AGENT STATE TIME) <= 11
AND LEAD(STATE) OVER (PARTITION BY AGENT NAME ORDER BY DATE, AGENT NAME) = 'Not Ready'
THEN 1
ELSE 0
END)

MichelleH

@agolla2 Beast modes don't typically work well with aggregating aggregate/window functions. I'd suggest calculating the LEAD function in an ETL so that you don't have to do it in beast mode

ArborRose

Make sure you have back ticks on your fields, and that your field names match the correct spelling.