3 Replies Latest reply on Apr 20, 2016 12:38 PM by Taylor Hintz

    Sorting an AM/PM Time dimension

    Taylor Hintz

      I've got a time dimension, and because I need to exclude the date it's a string type dimension. I also need it to stay in the format of AM/PM. Because it's a string in this format, the function for sorting it in an ascending/descending order creates an order of "6:00 AM... 6:00 PM...6:01 AM...6:01 PM...6:02 AM..." and so on. Is there a way to sort my Time dimension as it should, without doing it manually for each record? I have records for every minute of the day, so that would be an incredibly time-consuming process.

        • 1. Re: Sorting an AM/PM Time dimension
          Jonathan Drummey

          Hi Taylor, I don't understand the piece about needing to use a string data type? Here's what I do, assuming that your dates start out as a date and time like 2015-04-19 02:00, 2016-03-12 18:46, etc.

          1) Create a myTime calc that is FLOAT([myDateTime]) - INT([myDateTime]). This gets only the time portion of the datetime.

          2) Format the myTime calc to be h:mm ampm.

          3) Put that in a view.

           

          Jonathan

          • 2. Re: Sorting an AM/PM Time dimension
            Ashish Singh

            Hi Taylor,

             

            I am sorting by strings and its working perfectly fine, may be because I have formatted the string in the format 'HH:MM AM/PM' while you have formatted your strings as 'H:MM AM/PM'

            'H:MM AM/PM' will show time-stamp as 6:01 AM

            'HH:MM AM/PM' will show time-stamp as 06:01 AM

             

            Also, possibly this HH and H may not be the case at all, but I have tima-stamps behaving crazy in Excel a lot of times because of formatting issues, so worth giving it a try.

             

            And then sort your time-stamp in descending order by alphabets.

             

            Sort.PNG

             

            I hope that works for you.

             

            Cheers!

            Ashish

            • 3. Re: Sorting an AM/PM Time dimension
              Taylor Hintz

              Jonathan,

               

              The reason my time dimension was a string type was because I was told

              earlier it was the only way to get rid of the random date that seems to be

              obligatorily added to the time. It looked like this:

               

              As you can see, I took up your advice of making a calculation which I named

              Time of Offense. I may not have followed your instructions correctly,

              however, as it seems to have just changed the date from the highest

              numerical date to the lowest. Here's my calculation:

               

              I am not sure what you mean by a myTime calculation, assuming that's a

              calculation type and not just an example name you've given it.

               

              On Wed, Apr 20, 2016 at 1:59 AM, Ashish Singh <tableaucommunity@tableau.com>