Issue with Nested Case Statements

I have a dataset of call records. I am attempting to filter out records that fall outside of business hours when the 'Call Result' is 'No Agents in Queue,' based on the product the call was associated to. I have created the following Beast Mode calculation to do so:

 

 

CASE `Last Leg Product`
WHEN 'NPAC-CCS' THEN
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
ELSE
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
END

 

 

 There is a filter on the card called to show only cases where 'No Agents Business Hours Filter' = 0. All other products are correct, but for 'NPAC-CCS' calls there are still cases outside of the defined business hours.

Best Answer

  • GrantSmith
    GrantSmith Indiana 🟤
    Accepted Answer

    Hi @MosesWynn 

    Are all of your NPAC-CCS records still appearing or are some records being filtered / identified correctly (Essentially - are you getting into the first section of your beast mode)? Is it possible there's whitespace around the `Last Leg Product` field (did you try and wrap it in a TRIM function)?

     

    Have you tried rewriting your CASE to be CASE WHEN `Last Leg Product` = 'NPAC-CCS'

    (they should be logically identical though)?

Answers

  • Hi @GrantSmith,

     

    Thanks for your response.

     

    'NPAC-CCS' records that fall outside of the span 8AM to 8PM are being filtered out, as per the ELSE clause. I reformatted to the following, but am still getting the same results.

    CASE 
    WHEN TRIM(`Last Leg Product`) = 'NPAC-CCS ' THEN
      (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
    ELSE
      (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
    END

     

    Thanks!

    Moses

  • GrantSmith
    GrantSmith Indiana 🟤

    Hi @MosesWynn 

     

    Did you mean to have the trailing space in 'NPAC-CCS '? Doing a TRIM on your column and comparing it to the value with a trailing space will never match up.

  • Actually, your suggestion worked. I'm realizing I accidentally had a space after CCS in my formula.

     

    Thanks!

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!