10 Replies Latest reply on Dec 14, 2015 1:11 PM by Muralidhar Areti

# Sort by Time in DateTime Data Type

Hi All,

I have Date& Time data for each employee punching in and out by day. I have a min([Punch In]) and a max([Punch Out]) because each employee punches in and out twice in a day. Once in the morning, once for lunch, once back in after lunch, and when they leave at the end of the day.

I would like to sort by time.

Any suggestions?

• ###### 1. Re: Sort by Time in DateTime Data Type

Hello Elizabeth,

If you are wanting to sort by total time between in and out, then you can use DATEDIFF to calculate the total number of hours or minutes, etc. as you'd like to see it.

• ###### 2. Re: Sort by Time in DateTime Data Type

Got a sample packaged workbook?

--Shawn

• ###### 3. Re: Sort by Time in DateTime Data Type

Elisabeth!

Find my first attempt in attached workbook versio 9.0

• ###### 4. Re: Sort by Time in DateTime Data Type

Hi Elizabeth,

Have you tried right-clicking on your time-stamped variable in Tableau and playing around with the various sorting options? You should be able to do a dynamic sort, as illustrated below:

In this case I right-clicked on my "Order ID" field on the rows shelf and selected "Sort..." to get to the sorting options seen above. In plain English, this selection is saying "sort this variable in descending order such that the highest/youngest dates are at the top and the lowest/oldest dates are at the bottom."

I hope this helps!

Elliott Stam

InterWorks

2 of 2 people found this helpful
• ###### 5. Re: Sort by Time in DateTime Data Type

Please see attached sample workbook. Ideally I would like to sort by hours and keep the day detail if possible

• ###### 6. Re: Sort by Time in DateTime Data Type

Hi Elizabeth,

Like this? Please see attached workbook (updated version of yours).

I just did the steps I had detailed above in an earlier post, performing the described dynamic sort.

Elliott Stam - InterWorks

1 of 1 people found this helpful
• ###### 7. Re: Sort by Time in DateTime Data Type

Hi Elliott,

Yes!

In your example, I did change the field from In Punch Min to In Punch Time with Minimum as the Aggregation and got the results I am looking for.

Thank you!!

• ###### 8. Re: Sort by Time in DateTime Data Type

Hi Elizabeth,

To clarify, do you want to keep the entire date/time value and sort by the hour to make your results look like the chart on the left instead of the chart on the right?

left: sorted by hour, right: sorted by date

• ###### 9. Re: Sort by Time in DateTime Data Type

Hi Muralidhar,

I was looking to have my datetime value sorted by hour - like this

• ###### 10. Re: Sort by Time in DateTime Data Type

It looks like you already got an answer while I was in the middle of composing mine! All I did was add hour and minute to the rows shelf, sort them both descending and then hide the header.

It is a different way to do it, mine gives you more partitions though.. In the end this is how mine looked: