Beast Mode - Grouping

Options
Stu5677
Stu5677 Member
edited July 7 in Calculations & Variables (Beast Mode)

Hi! I am trying to fix this beast mode calculation

2024-07-15 09_54_58-Domo.png 2024-07-15 09_54_37-Domo.png

to group properly. I need the time to group 12am-6am and then show 7am-7pm as separate lines and then group 8pm-11:59pm. The opened date column i am using is in this format '2023-10-22T07:18:17' which is why I am trying to use the beast mode, but the grouping doesn't seem to be working as expected, any suggestions?

Tagged:

Best Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    The issue you're having might be related to how the hour (%k format) is being evaluated for ranges.
    DATE_FORMAT function returns a string, not an integer. What about something like…

    CASE
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 0 AND NUMBER(DATEPART('hour', Opened Date)) < 6 THEN '12am-6am'
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 6 AND NUMBER(DATEPART('hour', Opened Date)) < 19 THEN CONCAT(NUMBER(DATEPART('hour', Opened Date)), 'am')
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 19 AND NUMBER(DATEPART('hour', Opened Date)) <= 23 THEN '8pm-11:59pm'
    
ELSE 'Other'
    
END

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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I believe you can have a more consolidated beast mode using the hour and concat functions and stacking the statements in the correct order

    CASE WHEN HOUR(startdate) < 6 THEN '12am-6am'
    WHEN HOUR(startdate) < 12 then CONCAT(HOUR(startdate),'am')
    WHEN HOUR(startdate) < 20 THEN CONCAT(HOUR(startdate)-12,'pm')
    ELSE '8pm-11:59m'
    END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • ColemenWilson
    ColemenWilson Coach

    Do you have a sort applied to your card? Remove it and see if it groups properly.

    If I solved your problem, please select "yes" above

  • ArborRose
    ArborRose Coach
    Answer ✓

    The issue you're having might be related to how the hour (%k format) is being evaluated for ranges.
    DATE_FORMAT function returns a string, not an integer. What about something like…

    CASE
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 0 AND NUMBER(DATEPART('hour', Opened Date)) < 6 THEN '12am-6am'
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 6 AND NUMBER(DATEPART('hour', Opened Date)) < 19 THEN CONCAT(NUMBER(DATEPART('hour', Opened Date)), 'am')
    
WHEN NUMBER(DATEPART('hour', Opened Date)) >= 19 AND NUMBER(DATEPART('hour', Opened Date)) <= 23 THEN '8pm-11:59pm'
    
ELSE 'Other'
    
END

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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I believe you can have a more consolidated beast mode using the hour and concat functions and stacking the statements in the correct order

    CASE WHEN HOUR(startdate) < 6 THEN '12am-6am'
    WHEN HOUR(startdate) < 12 then CONCAT(HOUR(startdate),'am')
    WHEN HOUR(startdate) < 20 THEN CONCAT(HOUR(startdate)-12,'pm')
    ELSE '8pm-11:59m'
    END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories