Beast Mode - Grouping

Stu5677 · 2024-07-15T14:58:35+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Charting

Formatting

Beast Mode

Formulas

Accepted answers

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

All comments

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