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

agolla2
agolla2 Member
in Charting

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.

Tagged:

Answers

  • ArborRose
    ArborRose Coach 
    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)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • agolla2
    agolla2 Member

    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
    MichelleH Coach

    @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
    ArborRose Coach

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories