Beast Mode - Grouping

Stu5677

Hi! I am trying to fix this beast mode calculation

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?

ArborRose

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

MarkSnodgrass

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

ColemenWilson

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

ArborRose

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

MarkSnodgrass

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