Beast Mode to Categorize the data based upon the pattern

agolla2

Hello,

Can anyone help me in writing this beast mode. Where I'm aiming to categorize these based on the status reversals from Yes to No.

Case when current ready status is No and previous status is Yes then considered as reversal.

Example like below: I already have first 3 columns in my data.

Tried this beast mode but not working.

CASE

    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 1 THEN '1 Time'


    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 2 THEN '2 Times'


    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) >= 3 THEN '3+ Times'


    ELSE 'No Reversal'

END

ggenovese

Hi - your original beast mode was missing the second aggregation, you needed SUM(SUM(…

CASE

    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 1 THEN '1 Time'


    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 2 THEN '2 Times'


    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) >= 3 THEN '3+ Times'


    ELSE 'No Reversal'

END

but also, I feel that you can get the same result without the window function

CASE 

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) = 1 THEN '1 Time'

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) = 2 THEN '2 Times'

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) > 2 THEN '3+ Times'

ELSE 'No Reversal'

END