Reply
Frequent Visitor
Posts: 3
Registered: ‎09-15-2018
Accepted Solution

Split the timestamp in to categories

[ Edited ]

I have few Request types. We can have have their status as Success, Failed, Expired.

 

I have these timestamps round the clock and I would like to filter the time between morning 10AM to 12PM in to 15 minutes intervals and group them in to categories like 10:00-10:15, 10:15-10:30. How do i do that ?

 

Is there any default slicer for time ? I am aware that date has but could not find time slicer. 

 

Example:

 

 

REQUESTSTATUSUPDATED_TS
MoveInSuccess14-SEP-2018 05:29
MoveInSuccess14-SEP-2018 05:20
MoveInSuccess14-SEP-2018 05:26
MoveInSuccess14-SEP-2018 05:19
MoveInSuccess14-SEP-2018 05:23
MoveInSuccess14-SEP-2018 05:26
MoveInSuccess14-SEP-2018 05:35
MoveInSuccess14-SEP-2018 05:32

 


Accepted Solutions
Advisor
Posts: 308
Registered: ‎01-16-2018

Re: Split the timestamp in to categories

@RMeghashyam,

 

Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:

 

TimeGroup = 
FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") & "-" & FORMAT(HOUR(Table2[Datetime column]),"00") & ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")

Where

- Table2[Datetime column] is a table and column of your datetime

 

Then you can add a simple slicer with this field.

 

Then it will look like below:

TimeGroup.JPGTimeGroup

 

 

 

 

 

 

 

 

 

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

View solution in original post

Advisor
Posts: 308
Registered: ‎01-16-2018

Re: Split the timestamp in to categories

[ Edited ]

@RMeghashyam,

 

I understand your point. See an updated approach:

 

Create 5 calculated columns:

 

HoursStart = Hour(Table2[Datetime column]) 

MinutesStart = ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15

HoursEnd = IF(Table2[MinutesStart]=45,Hour(Table2[Datetime column])+1,Hour(Table2[Datetime column]))

MinutesEnd = IF(Table2[MinutesStart]+15=60,0,Table2[MinutesStart]+15)

TimeGroup = FORMAT(Table2[HoursStart],"00") 
& ":"
& FORMAT(Table2[MinutesStart],"00")
& "-"
& FORMAT(Table2[HoursEnd],"00")
& ":"
& FORMAT(Table2[MinutesEnd],"00")

Then it will behave as below:

TimeGroup 2.JPGtime group 2

 

 

 

 

 

 

 

 

 

 

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

View solution in original post


All Replies
Advisor
Posts: 308
Registered: ‎01-16-2018

Re: Split the timestamp in to categories

@RMeghashyam,

 

Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:

 

TimeGroup = 
FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") & "-" & FORMAT(HOUR(Table2[Datetime column]),"00") & ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")

Where

- Table2[Datetime column] is a table and column of your datetime

 

Then you can add a simple slicer with this field.

 

Then it will look like below:

TimeGroup.JPGTimeGroup

 

 

 

 

 

 

 

 

 

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Frequent Visitor
Posts: 3
Registered: ‎09-15-2018

Re: Split the timestamp in to categories

Thanks so much for help, but how do we modify 60's. Eg: 9:60. 10:60 to 10:00, 11:00 respectively

Advisor
Posts: 308
Registered: ‎01-16-2018

Re: Split the timestamp in to categories

@RMeghashyam,

 

10:60? What do you mean? For 10 hours maximum is 10:59. Provide more details.

 

Regards,
Ruslan

Frequent Visitor
Posts: 3
Registered: ‎09-15-2018

Re: Split the timestamp in to categories

I mean as per your logic, The grouping is done properly, but for times between HH:45 to HH:60 they are getting displayed as 09:45 - 09:60

10:45-10:60

 

Is there a way that we can categorize them as 09-45-10:00 etc for all the time stamps. 

Advisor
Posts: 308
Registered: ‎01-16-2018

Re: Split the timestamp in to categories

[ Edited ]

@RMeghashyam,

 

I understand your point. See an updated approach:

 

Create 5 calculated columns:

 

HoursStart = Hour(Table2[Datetime column]) 

MinutesStart = ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15

HoursEnd = IF(Table2[MinutesStart]=45,Hour(Table2[Datetime column])+1,Hour(Table2[Datetime column]))

MinutesEnd = IF(Table2[MinutesStart]+15=60,0,Table2[MinutesStart]+15)

TimeGroup = FORMAT(Table2[HoursStart],"00") 
& ":"
& FORMAT(Table2[MinutesStart],"00")
& "-"
& FORMAT(Table2[HoursEnd],"00")
& ":"
& FORMAT(Table2[MinutesEnd],"00")

Then it will behave as below:

TimeGroup 2.JPGtime group 2

 

 

 

 

 

 

 

 

 

 

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!