Forum Discussion
Post ProdigyHow to Show 20minute Intervals
Evening,
I'm trying to show on my axis the time of the day in 20 minute intervals. At the moment I have a time table which has the time by the second as well as a second column which I've extracted the time by the start of the hour.
I was wondering if anyone new of a way to format or configure as to show for example:
7.00am-7.20am
7.20am-7.40am
7.40am-8.00am.
My best thought was to use a conditional column in order to calculate this; but considering my time column in terms of granuality the highest possible would be "7:21am"; I would I have to do If 'time' contains "7.21am" = 7.40am. And even then, I'm not sure I would be able to filter and sort it appropriatly.
Thoughts and feelings?
How about
- You create a new "TimeSlot" column in your time table that calculates the time slot
TimeSlot =
SWITCH(
TRUE(),
HourMinuteSecond[Minute] < 20,HourMinuteSecond[Hour12]& ".00"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".19"&HourMinuteSecond[AMPM],
HourMinuteSecond[Minute] < 40,HourMinuteSecond[Hour12]& ".20"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".39"&HourMinuteSecond[AMPM],
HourMinuteSecond[Hour12]& ".40"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".59"&HourMinuteSecond[AMPM]
)- To make sure the timeslots are ordered correctly you need another column calculating a sortorder. someting like
TimeSlotSortOrder = HourMinuteSecond[Hour24] * 100 + SWITCH( TRUE(), HourMinuteSecond[Minute] < 20,1, HourMinuteSecond[Minute] < 40,2, 3 )- Now you can set the "Sort by Column" property of the "TimeSlot" column to the "TimeSlotSortOrder" column
Hope this helps
JJ
6 Replies
- DoubleJ
Solution Supplier
How about
- You create a new "TimeSlot" column in your time table that calculates the time slot
TimeSlot =
SWITCH(
TRUE(),
HourMinuteSecond[Minute] < 20,HourMinuteSecond[Hour12]& ".00"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".19"&HourMinuteSecond[AMPM],
HourMinuteSecond[Minute] < 40,HourMinuteSecond[Hour12]& ".20"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".39"&HourMinuteSecond[AMPM],
HourMinuteSecond[Hour12]& ".40"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".59"&HourMinuteSecond[AMPM]
)- To make sure the timeslots are ordered correctly you need another column calculating a sortorder. someting like
TimeSlotSortOrder = HourMinuteSecond[Hour24] * 100 + SWITCH( TRUE(), HourMinuteSecond[Minute] < 20,1, HourMinuteSecond[Minute] < 40,2, 3 )- Now you can set the "Sort by Column" property of the "TimeSlot" column to the "TimeSlotSortOrder" column
Hope this helps
JJ
ElliotPMorning,
Thank you so much for the response. The TimeSlot column works and is 100% what I was looking for thank you so much.
When I tried to the sort the Timeslot column by the Timeslotsortorder column it gave me:
https://gyazo.com/2d7826c15b41e0f29617262e64edddb6
Which is a bit bizare; I sorted it by the Hour12 column and its filtering perfectly; thank you so much.
- ElliotP
This next part would probably be better suited to it's own post; but I'll post it here to see how it travels.
How do you think we would be able to create a rolling time window based upon sales.
As so for example; we could find the 30minute window on Monday where the most sales occured within a thirty minute window, so it might be 11.21-11.51am or 2.08-2.38pm for example?
- CahabaData
Memorable Member
I don't fully understand your post - let me admit that up front.
Your static time table has 24 x 60 x 60 = 86400 records I believe. And it sounds like you have 3 x 24 = 72 unique intervals in a day - and so there is a column in your static time table for the unique Interval ID and in each record of the table one of the 72 unique interval ID values is entered.
Then in your data set table there is a time value field that can join to the time value field in the static time table - and you will have the adjoining field of the Interval ID available to you for reporting.
