6 Replies Latest reply on Dec 28, 2011 10:49 PM by mythilikaranam

    Calculated Time Interval Between Actions

    Mel Stephenson

      In the attached packaged workbook there are two views - one of the raw data (excluding nulls) and one filtered down to show the timestamps for the action that completes each cycle. The data by the way is the transaction log for a number of workers who perform a sequence of  tasks on uniquely numbered boxes. There is lots of analysis I would like to do around the time taken for each of the actions as well as the time each user takes to complete each box.

      How do I get Tableau to calculate the time interval between specific time stamps? So for example, how would you calculate the time interval between time stamps at the transaction log id level or the action id level or the box level?

      Everything about it says table calc but table calcs and dates - do they play together?

        • 1. Re: Calculated Time Interval Between Actions
          Joe Mako

          I am not quite sure understand what you are ultimately looking for, but is a calc field like:



          MIN([Added DateTime])-LOOKUP(MIN([Added DateTime]),-1)


          what you need to get closer?


          I modified one of your sheets to make use of this calc field.


          Depending on what your ultimate goal is and how you want to look at these numbers in aggregate, the calculation and partitioning would be different.

          • 2. Re: Calculated Time Interval Between Actions
            Richard Leeke

            I've done you a couple of views demonstrating the sorts of things you can do, Mel.  A few comments.


            1) I've assumed that the timestamps represent the completion times for actions, so the duration is worked out as the time from completing the previous action, using LOOKUP([timestamp], -1).  If the timestamps are actually start times you need to look up the next start time, using LOOKUP([timestamp], 1).


            2) I've assumed that there are no pauses between boxes - so the duration of the first action on box 2 is found by subtracting off the timestamp when the last action of box 1 finished.


            3) There is no way to calculate the duration of the very first action (you only know the end time).  The way I've done the higher level aggregates also doesn't work out the duration for the first box (or whatever), since you don't the end time for the previous.  You could get tricky and work out the time for the first box except for the first action for that box - but as it still wouldn't be accurate I haven't bothered.


            4) Duration is expressed in fractional days - that's what you get if you subtract two datetimes and that's what you need to make a Gantt chart like this work.


            5) The reason for the field MinusDuration is that a Gantt chart expects a start time and a duration.  As we have an end time and a duration, we need to make the duration negative to show the bar in the correct place (i.e. to the left of the end time).


            6) [DurationSeconds] just divides the duration in fractional days by the number of seconds in a day (86400) to make the tooltips more useful.


            7) I turned on mark borders to highlight where there are multiple consecutive copies of the same action for one box.


            Let me know if you have any questions.  I use this sort of representation for time-series data all the time - so I may be skipping over something that seems obvious to me but wasn't so obvious the first time round.

            1 of 1 people found this helpful
            • 3. Re: Calculated Time Interval Between Actions
              Mel Stephenson



              Many thanks for this - it was absolutely spot on. Sorry that it's taken me so long to come back and thank you (as always!) for your rapier insight into the elegant solution to the problem. Right! Time to put another question up there ...

              • 4. Re: Calculated Time Interval Between Actions
                guest contributor

                Is there a way a caluclated field can have the following manipulation


                Volume  = (xyz*(2011Q3) + abc*(2009Q4))/integer


                where 2011Q3 = Measure xyz in the year 2011 and quarter Q3

                          2009Q4 = Measure abc in the year 2009 and quarter Q4

                • 5. Re: Calculated Time Interval Between Actions
                  Richard Leeke

                  You should start a new thread - this isn't in any way related to Mel's original question, I don't think.


                  When you do, it would be helpful if you could provide a little context for your question.  The short answer is "yes, lots of ways".  With a bit more context people will be able to guide you towards the most appropriate option.

                  • 6. Re: Calculated Time Interval Between Actions

                    any move on this?