I recently created a weekday and half-hour heatmap using Beast Mode functions to break timestamps down to half-hour increments. But for some reason, the chart is sometimes sorting the half hours into the wrong order.
Notice how 3:30 PM appears before 3:00 PM in the chart below, as does 830 PM before 8 PM and 930 PM before 9 PM:
The source columns I'm using to calculate the half-hour intervals are datetime columns, and the formula I'm using to calculate half-hour increments is:
TIME_FORMAT(CONCAT(HOUR(
case
when `Call ID` is not null then `Call Create Date`
when `Comments ID` is not null then `Comment Create Date`
when `Chat ID` is not null then `Chat Session Start Date`
else `Ticket Create Date`
end
), ':',
CASE
WHEN
FLOOR(MINUTE(
case
when `Call ID` is not null then `Call Create Date`
when `Comments ID` is not null then `Comment Create Date`
when `Chat ID` is not null then `Chat Session Start Date`
else `Ticket Create Date`
end
)/30)*30 = '0' then '00'
ELSE '30'
END
),'%h %i %p')
And the Beast Mode formula I'm using to calculate Interaction Hour Is:
hour(case
when `Call ID` is not null then `Call Create Date`
when `Comments ID` is not null then `Comment Create Date`
when `Chat ID` is not null then `Chat Session Start Date`
else `Ticket Create Date`
end)
Then I sort by
Weekday, Interaction Hour, and Interaction Half Hour in that order.
Any ideas what could be causing the sorting issue?
