8 Replies Latest reply on Mar 18, 2018 3:01 PM by Don Wise

    Working with time durations >24hrs and involving multiple timestamps

    Mark Houareau

      Hi Folks, apologies if this is covered elsewhere but I have scanned the forums and are struggling to find an exact example of what I am seeking to achieve.

       

      I have an historical archive of assessments made on potential clients. There are 5 key timestamps recorded in Excel that have the format of d mmm, yyyy h:mm AM/PM

       

      I have worked out in Excel already the duration between the first and last timestamps which are essentially start and finish times of the assessments. I have been using the NETWORKDAYS function to set core business hours of 8:30am-5:00pm Mon-Fri. So yes, I have the correct information in hh:mm format in Excel. What I have garnered from the forums here is Tableau struggles to display times that exceed 24:00 of which I have many.

       

      I have tried using Power Query in Excel to convert the duration times to total seconds, total days etc but I struggle in Tableau to get the data to display once I convert it to a hh:mm:ss or dd:hh:mm:ss format. I assume I am meant to do this by formatting the number display of the measure? I tried to create a calculated field as well but to no avail.

       

      I am a total noob with coding in Tableau and have attempted to copy/paste/adjust code to suit my needs.

       

      I have two key measures: Assessment Time and Handling Time.

       

      In the example below I have the raw data coming through from Excel when I have converted the duration to seconds. This is a SUM of all recorded assessments broken down by State. The first number is the total seconds for assessment (assessment time) and the second is the total handling time.

      If I switch the values from SUM to AVG I get the following result which is fine:

       

       

      The problem then is when I convert these numbers via formatting to hh:mm:ss

       

      And again if I change it to dd:hh:mm:ss. It just doesn't look right.

       

      I'm assuming this is because the total seconds exceeds 24hrs so given the information provided what should I be doing? Please bear in mind I am brand new to Tableau so you really will have to explain where I code and where I click etc. This has been my biggest headache so far in Tableau having recently moved away from Power BI. Loving everything so far except this bit lol

       

       

      Questions I have:

      • Am I wasting my time creating the data for duration in Excel when I could use Tableau to achieve this using the dates in d mmm, yyyy h:mm AM/PM format? If I am wasting my time how do I go about achieving this in Tableau? I have seen some suggestions on the forums but I'm not sure where that code goes? Does it go in a calculated field or somewhere else?
      • Should I be bringing in the Excel creation duration data in a different format to Total Seconds?
      • Whatever the solution is I need to have it work in multiple instances on the data so any further assistance on how to separate and achieve this would be greatly appreciated:
        • Duration between the start (1) and finish (5) times.
        • Duration between time 1 and 2
        • Duration between time 2 and 3
        • Duration between time 3 and 4
        • Adding the duration between time 2 and time 3 to the duration between 4 and 5.

       

      Update: I've attached a sample of the data in Excel. Note that in some examples timestamps 3, 4 and 5 can be the same.