How do I sort my time of day ranges on a card so it is in chronological order?

user028582
user028582 Member
edited March 2023 in Cards, Dashboards, Stories


I created a time of day beast mode below which works successfully. I am using a bubble chart to show the time od day most events occur. This also works successfully however, I would like to be able to sport my time of day in chronological order. I have tried to use the beast mode created below in the sort as both assending and decending but it is not giving me the times in chronoolocal order on the chart.


CASE 

WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('6:00 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('7:00 am') THEN '6:00am-7:00am'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('7:01 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('8:00 am') THEN '7:00am-8:00am'

  WHEN TIMESTAMP(`EVENTTIME`)>= TIMESTAMP('8:01 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('9:00 am') THEN '8:00am-9:00am'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('9:01 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('10:00 am') THEN '9:00am-10:00am'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('10:01 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('11:00 am') THEN '10:00am-11:00am'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('11:01 am') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('12:00 pm') THEN '11:00am-12:00pm'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('12:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('1:00 pm') THEN '12:00pm-1:00pm'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('1:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('2:00 pm') THEN '1:00pm-2:00pm'

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('2:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('3:00 pm') THEN '2:00pm-3:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('3:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('4:00 pm') THEN '3:00pm-4:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('4:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('5:00 pm') THEN '4:00pm-5:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('5:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('6:00 pm') THEN '5:00pm-6:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('6:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('7:00 pm') THEN '6:00pm-7:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('7:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('8:00 pm') THEN '7:00pm-8:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('8:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('9:00 pm') THEN '8:00pm-9:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('9:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('10:00 pm') THEN '9:00pm-10:00pm'  

  WHEN TIMESTAMP(`EVENTTIME`)> TIMESTAMP('10:01 pm') AND TIMESTAMP(`EVENTTIME`)<= TIMESTAMP('11:00 pm') THEN '10:00pm-11:00pm'  

   

    ELSE 'Overnight (11:01pm-5:59am)' END

Not in Chronological Order:

image.png


Tagged:

Answers

Categories