Heatmap in 30 Minute Intervals

pstrauss
pstrauss Member
in Charting

I've created a heatmap based on the timestamp of records with the day of week on the Category 1 axis and the hour of day on the Category 2 axis. However, the business is asking for a version that breaks things down into 30 minute intervals. I'm currently using the HOUR function on the timestamp to extract the hour, but I'm not sure how I could bucket these into 30 minute intervals.

Any ideas?

Tagged:

Comments

  • GrantSmith
    GrantSmith Coach

    You could do something like:

    CONCAT(HOUR(`Timestamp`), ':', FLOOR(MINUTE(`Timestamp`)/30)*30)

    Minute will return the number of minutes in the timestamp (0-59)

    /30 will return a number between 0 and < 2

    FLOOR will convert your fraction to 0 or 1.

    *30 will convert the interval to either 0 or 30. This will then give you your half-hour buckets.


    You may need to extract the hour and minute to separate beast modes in order to sort based on the hour and then the 30 minute bucket so that it's sorted properly.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • pstrauss
    pstrauss Member

    Thanks @GrantSmith that's a good idea.

  • pstrauss
    pstrauss Member

    Managed to make it work. Thanks @GrantSmith

    Screen Shot 2023-02-16 at 5.42.09 PM.png


Categories