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 to any 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 to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive