How to Show 20minute Intervals

[ElliotP]

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?

[CahabaData]

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.

[DoubleJ · Accepted solution]

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

[ElliotP]

Morning,

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.

[DoubleJ]

Hi Elliot

Glad I could help!

You have to make sure that entries that make up the order column are unique. I checked your sample data: there are several entries with identical values for hour and minute (starting at 11 am). I guess thats the reason for that message.

JJ

[ElliotP]

I'm trying to do a 30min slot, I've got the TimeSlot measure working perfectly (thank you), but I'm unable to create a TimeSlotOrder to work with it. I'm not sure how to do it as to avoid the typical sorting error. Thoughts?

[ElliotP]

@DoubleJ

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?