7 Replies Latest reply on Jun 28, 2018 12:35 PM by Mavis Liu

    24 Hour Format by Day (Tableau 10.3)

    Sneha Darsi

      Hi All,

      So the goal of what I'm trying to do is shown in the image attached. Basically, the user can look up a date and see how long a job lasted in hourly intervals (in a 24 hour cycle). The problem I'm having is two-fold: how do I get the 24 hour format at the bottom (as shown in the picture) to where it updates depending on the date selected and how do I get the start time/end times to show up as time elapsed. For the 2nd one, it was suggested that I do this formula ((MAX([End Datetm])- MIN([Start Datetm]))  But I'm not exactly sure where to go from here. I attached the workbook and what I have so far, but any help on how to get to the final result (in the pic) would be great. I realize that this is likely a multi-step process and I'm certainly not expecting anyone to help with the entire process, but I would really appreciate just a general guidelines of how to get to where I need to go from what I have so far. Thank you so much!

       

      Update: I attached an updated workbook. I now have a dropdown calendar where the user can select a date. I also have the difference in run time (from start to end time). The big issue I'm still having is getting the drop down calendar to update with the run times so that when you select a date off the drop down it will show you only that day's run times in hourly intervals. As of now, clicking any date on the calendar doesn't do anything. The goal, for example, is instead of seeing an elapsed total time of 3,337 for CIS on 5/23 at 12 AM (they are all 12 AM for some reason), you could click on 5/23 in the calendar and see the specific times it ran (say from 9AM to 11AM). Just to repeat, any help big and small would be very much appreciated!! And a shout out to Mavis for her help so far, thank you!

        • 1. Re: 24 Hour Format by Day (Tableau 10.3)
          Mavis Liu

          Hi Sneha,

           

          You can right click on the date and format to date and 24 hour clock:

           

          2018-06-28_09h59_17.png

           

          You can select the one you want or enter in a custom option:

           

          2018-06-28_09h59_17.png

           

          To work out the different, create a datediff calculation based on the start and end date. Then we will find an average because there are multiple start and end dates for each source system.

           

          2018-06-28_10h03_59.png

           

           

           

          Then bring your start date into rows and add the datediff into size to get this:

           

          2018-06-28_10h03_12.png

           

          So for bus datetime 14/05/2018, Source system MGO, the average time difference was 1655 hours as the date started on 08/03/2017 12:25 and ended on 16/05/2018 11:00.

          Please see the attached workbook.

           

          Thanks,

           

          Mavis

          • 2. Re: 24 Hour Format by Day (Tableau 10.3)
            Sneha Darsi

            Hi Mavis,

            Thank you for the help, much appreciated! Do you know if there is a way to look at this information from a daily view? As in the user can select a date from a drop down and then the viz will update based on that day? I attached a pic of the drop down date format that I'm trying to create. When you click on the View Date box, a dropdown calendar opens up and you can click on any date on any month that fits the data. I've heard that I'm supposed to create a parameter in order to be able to get that dropdown, do you know if that is true? Thank you again, this is really helpful for me!Capture 5.PNG

            • 3. Re: 24 Hour Format by Day (Tableau 10.3)
              Mavis Liu

              Hi Sneha,

               

              Sorry I'm not sure I quite understand what you're looking for. You could use a parameter, but wouldn't a normal day date filter work in your case?

               

              Thanks,

               

              Mavis

              • 4. Re: 24 Hour Format by Day (Tableau 10.3)
                Sneha Darsi

                Hi Mavis,

                So the ideally, what the view is supposed to do is you can click on any date off the drop down calendar and see in a 24 hour format the start to end times of the source systems. It was not specified to me exactly what format the date range selection should be (whether a filter, slider, etc). But I picked dropdown calendar because the date range is fairly large and this would be easier to select from compared to a slider or just a list filter. I attached the updated version of the workbook. I created a parameter to get the dropdown calendar, but I'm struggling with getting it to update with the run time. I'm thinking I need to do some calculation to get it to update, but I'm not sure how that works. Does this help clarify? Thanks again

                • 5. Re: 24 Hour Format by Day (Tableau 10.3)
                  Mavis Liu

                  Hi Sneha,

                   

                  Ah I see I get it, so with your parameter, you've created it but a parameter just holds a set of values.

                   

                  At this point it's not linked to the data in any way, so to link it to the dataset you'll need to use a calculated field and put in:

                  2018-06-28_19h13_15.png

                  The above is saying the date (not taking into account the time) matches between the bus date and the view date parameter. Next bring this field into filters and set it as true.

                   

                  Thanks and please see the attached workbook,

                   

                  Mavis

                  • 6. Re: 24 Hour Format by Day (Tableau 10.3)
                    Sneha Darsi

                    Thank you! That is what I was trying to do for the longest time. You mentioned that this doesn't take into account time, would I just use the same datetrunc formula using 'hour', 'minute', and 'second'? Thank you again for taking the time, I was banging my head against the wall, so much appreciated!

                    • 7. Re: 24 Hour Format by Day (Tableau 10.3)
                      Mavis Liu

                      Hi Sneha,

                       

                      So your bus date is currently a datetime field, ie it would be something like 21/06/2017 10:33:55, yet your date parameter is just a date: 21/06/2017.

                       

                      So if you were to try bus time = view date then you wouldn't get any results, because they will not be exactly matching as one has time and the other doesn't.

                       

                      So this is why I used datetrunc, the datetrunc just truncates the date to the level you're telling it to. In this case - day.

                       

                      So the datetrunc then converts it to 21/06/2017 bus date = 21/06/2018 view date. Now they're matching, you will now see results.

                       

                       

                      Thanks,

                       

                      Mavis

                      1 of 1 people found this helpful