Heatmap Time Sorting Issue

pstrauss

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?

MarkSnodgrass

I find it best to use a numeric sort for the heat map. I would create a beast mode for the day of the week that returns the number (1-7) for the weekday, I would also do beast mode that returns a numeric value for the time.

pstrauss

@MarkSnodgrass so I tried converting the values to numbers for the hours but that doesn't work because I'd dealing with AM and PM - so “100” AM and “100” PM get grouped together in the chart.

I'm still perplexed as to why SOME hours sort properly and others don't when using a text sort. I've also tried converting the field to time/date format with no luck.

I welcome other suggestions.

MarkSnodgrass

@pstrauss I was thinking you would convert the time to a military time so that there is no confusion between 1am and 1pm. I haven't tried it, but would think you could use the time_format function to do this. You can use this page as guide to the proper formatting.

https://www.w3schools.com/sql/func_mysql_time_format.asp

Also, you might try adjusting which is first in your sorting properties, the field in the x-axis versus the y-axis. I have seen that have an affect on how the heat map renders.