Heatmap Time Sorting Issue

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?
Comments
-
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.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@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.
0 -
@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.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive