3 Replies Latest reply on Jun 30, 2018 7:36 AM by Zhouyi Zhang

    Date and Time Formatting

    Sneha Darsi

      Hi, I've been working on creating a view that mirrors the one below. I attached a workbook of what I have so far. The objective would be to select a date from the View Date box and then see how long a source system ran on that day (say from 6 AM to 10 AM and then maybe again from 8PM to 11PM). I feel like I have something that is close, but isn't right. And I have two concerns/questions that I'm hoping anybody can answer/help me with. 1. The dates are coming from the Bus Date column within my data source. As you can see in the image, there is a 24 hour format applied on the X-axis. I haven't been able to replicate that using the Bus Date column. I looked at the data and saw that only the date is displayed, not a time. So if you ask for hours on Bus Date, it sets it at 12AM for every instance. So, my 1st question is is there a way to still get the 24 hour format but still use Bus Date as the date selection format, maybe a way to derive it from the start time/end time columns? My 2nd question has to do with the formula I used to get the run time (the time between start time and end time). I used DateDiff in hours and I'm concerned that it's the wrong formula to use. Doesn't DateDiff just give the total elapsed time between 2 times? So would it accurately give me times on a daily basis? For example, say CIS ran from 6AM-9AM and then again from 5PM-7PM, isn't it just giving me 5 hours total instead of showing a block of 3 hours and then a block of 2 hours? Is there a formula to do the latter? Any help, no matter how big or small on either question would be very helpful! Please let me know if I can elaborate on anything. Thank you very much!

      Capture 4.PNG

        • 1. Re: Date and Time Formatting
          Zhouyi Zhang

          Hi, Sneha

           

          Is something like this?

           

          I attached the workbook for your reference.

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Date and Time Formatting
            Sneha Darsi

            Hi Zhouyi,

            This is close to what I need, but I think the problem is that it is not calculating the end times in a daily format (24 hour). For example, if you click on March 23rd, it should be showing only the sources that ran on that date and for how long. But instead it is showing me from March 23rd until May 28th. If I change the format of the tick marks on the X-axis to only show one day (meaning 3/23/2018 12:00:00 AM to 3/23/2018 11:59:59 PM, it gives me the 24 hour format I'm looking for on the X-axis (as shown below, apologies my Tableau functionality is limited for the moment, so I can't send you the workbook, but it is the same one you sent me). The problem with this though is that the View Date selection no longer matches with the date on the X-axis and no matter what date you select, it shows each source running the entire day, which is obviously not true (we can tell from the start/end times that this is not true). Do you know how to fix this? I've been trying to figure it out and haven't had any success. Thank you!Tab Pic.PNG

            • 3. Re: Date and Time Formatting
              Zhouyi Zhang

              Hi, Sneha

               

              To filter the view within only 1 day, you can create a filter as shown below.

               

              But I can't do the formatting of 24 hours in x-axis

               

              ZZ

              1 of 1 people found this helpful