8 Replies Latest reply on Oct 25, 2016 8:54 AM by Victor Baranov Branched to a new discussion.

    Set time as a range slider

    siyang.teo
    siyang.teo Aug 11, 2016 9:28 PM

      Hi, I have two questions on time filter. I have attached a sample twbx.

       

      1) Time Range Slider

      I am struggling to find a way to display time (00:00 to 24:00) as a range of time filter.

       

      This allows a person to filter the timing e.g., 07:00 to 10:00, so that he can focus on the graph on those times.

       

      This is possible with dates but it doesn't appear to be possible to just the time.

       

      I first isolate the time from a date time field by convert it to string and extracting the last 8 values which contains the time information.

      RIGHT(STR([Date Time]),8)

       

      I'm stuck at this stage. Can anyone help? Thanks!

       

       

      2) Time Interval Filter

      The 2nd question is how to use this time field to aggregate intervals of defined values, example: by 15mins, 30mins, one hour. The resulting axis should still display the time.

       

      Thanks!

        • 1. Re: Set time as a range slider
          Stoyko Kostov
          Stoyko Kostov Employee Aug 12, 2016 2:44 PM (in response to siyang.teo)

          Hi Teo,

           

          To your first question: It doesn't look like it's directly possible. Sliders (ranges) are not available for string values.

           

          The closest workaround I can think about is the following:

           

          Create parameters StartTime and EndTime. Choose Type as String, allowable values - All, current value - 00:00:00 for StartTime and 23:59:59 for EndTime.

          For each parameter, choose "Show Parameter Control".

           

          Now, move Time STR to Filter, choose Condition, and enter the following formula:

           

          [StartTime]<=[Time STR] AND [EndTime]>=[Time STR]

           

          This will filter the times according to the parameters, and at the same time gives users the power to control the parameters (the endpoints of the time range).

           

          To your second question: One possibility is to compute time as a number (minutes), and then use bins.

           

          Create a calculated field Time Minutes with definition INT(RIGHT(STR([Date Time]),2))/60+INT(LEFT(RIGHT(STR([Date Time]),5),2))+INT(LEFT(RIGHT(STR([Date Time]),8),2))*60

           

          Create a parameter Bin Size in Minutes, set its type to Integer, choose Current Value 15 and allowable values - a range with minimum 15, maximum 60, step size 15 (for example; you can choose anything you need). Show parameter control.

           

          Right-click on the new field Time Minutes, create bins, choose the parameter (Bin Size in Minutes) as the size of bin.

           

          You are all set - you can aggregate by bins, and users have control on setting the desired bin size. The only disadvantage is that time is shown as a number in minutes - not like hh:mm (e.g. 1260 instead of 21:00).

           

          Please see the examples implemented in the workbook I'm attaching.

           

          Hope this helps. Let me know if you have more questions.

          1 of 1 people found this helpful
          • 2. Re: Set time as a range slider
            siyang.teo
            siyang.teo Aug 13, 2016 4:11 AM (in response to Stoyko Kostov)

            Hi Kostov,

             

            The first solution is quite ingenious! While I was hoping for a slider, this could be a possible alternative.

             

            The 2nd solution is something which I had done too.

             

            I did not state this earlier (pardon me), but I need both of these filters being applied on the same axis or dimension. What do you reckon?

             

            It really sucks that Tableau did not make time as much flexibility as date. Which is strange considering its importance.

             

            Thank you!

            • 3. Re: Set time as a range slider
              Yuriy Fal
              Yuriy Fal Ambassador Aug 13, 2016 4:11 AM (in response to Stoyko Kostov)

              Hi all,

               

              Another option is to truncate date times to the epoch

              and use a 'one-day-fits-all' field for both a slider and bins.

               

              Please find the attached.

               

              Yours,

              Yuri

              2 of 2 people found this helpful
              • 4. Re: Set time as a range slider
                siyang.teo
                siyang.teo Aug 14, 2016 4:05 AM (in response to Yuriy Fal)

                Wow thanks Fal, the calculation looks complicated. Let me digest a bit first.

                 

                But it seem to work the way I wanted. Thanks

                • 5. Re: Set time as a range slider
                  siyang.teo
                  siyang.teo Aug 15, 2016 7:25 AM (in response to Yuriy Fal)

                  Hi Yuriy Fal,

                   

                  Can I ask what is the reason behind putting this specific date "#1900-01-01#"  in your calculated field?

                   

                  DATEADD('second', DATEDIFF('second', DATETRUNC('day',[Date Time]), [Date Time]), #1900-01-01#)

                   

                  Thank you.

                  • 6. Re: Set time as a range slider
                    Yuriy Fal
                    Yuriy Fal Ambassador Aug 15, 2016 7:46 AM (in response to siyang.teo)

                    Hi SIYANG,

                     

                    Nothing special, it could be any date --

                    TODAY() would be convenient as well.

                     

                    Yours,

                    Yuri

                    • 7. Re: Set time as a range slider
                      siyang.teo
                      siyang.teo Aug 15, 2016 8:20 AM (in response to Yuriy Fal)

                      Thanks Yuriy Fal!

                       

                      Unfortunately I encountered more difficulties in the same issue after diving deeper.

                       

                      If you are able to spare some more time, can you kindly take a look?

                       

                      The post is here time slider and time bin filters on a single time (hh:mm) axis

                       

                      Thank you.

                      • 8. Re: Set time as a range slider
                        Victor Baranov
                        Victor Baranov Oct 25, 2016 8:54 AM (in response to siyang.teo)

                        This worked for me:

                         

                        1. Create a calculated field "Time of Day" using formula: "MAKETIME(DATEPART('hour',[your datetime]), DATEPART('minute',[your datetime]),0)" - this will result in a span of datetime values (unfortunately there is no "time" type in Tableau 9.1 that I used) equivalent to a single date, thus limiting your time slider domain to one day.
                        2. Right-click on the newly created "Time of Day" calculated field -> Default Properties -> Date Format -> Custom -> "hh:mm" - this will limit formatted representation of these datetime values to just hour and minutes (in military time 00:00...23:59).
                        3. Drag the "Time of Day" to the filter shelf and choose the default "Range of Values" representation.
                        4. Show quick filter.

                         

                        Hope that helps,

                        Victor.