2 Replies Latest reply on Jul 5, 2016 2:10 PM by Lamia Z

    Sum of measure in time intervals

    Lamia Z

      Hello,

      I have a question regarding how to deal with time intervals.

      I have two data files. One contains 3 columns: Date, Time (format Hour:Min:Sec) and a given measure W which is a float. Note that W is measured every 2 hours and that the measure for example at 10am corresponds to the sum of what happened between 8am and 10am.

      The second file has 6 columns: Date, and 5 Times in the day (all format Hour:Min:Sec) Event1, Event2, Event3, Event4 and Event5.

      I would like to get the sum of W each day in each time interval (between Event1 and Event2, between Event2 and Event3 etc..).

      Is that feasible in Tableau?

      Also, to complicate things further, if a W is measured for example at 4pm, and therefore falls in between an event for ex. at 3:16pm and one at 5:02pm, only (4pm-3:16pm)/2h=44min/120min=37% of W should be included in the interval. The remaining would fall into the previous interval. So basically, we just want the intersection.

      Any help would be greatly appreciated.

      Otherwise, i'll do this before getting the data into Tableau.

      Thank you!

        • 1. Re: Sum of measure in time intervals
          swaroop.gantela

          Lamia,

           

          Please see if the attached could be a starting point for you.

          It required me to restructure your data as follows:

          Sheet 1: Date-Time, W

          Sheet 2: EventNumber, EventStart, EventEnd

          Sheet 3: StartInterval, EndInterval (the 2 hour intervals)

           

          I put a Key of 1 for every row and merged all the Sheets by that key.

           

          I then filtered to only those intervals containing and event

          ([Event Start]>=[Start Interval] AND [Event Start]<=[End Interval])

          OR

          ([Event End]>=[Start Interval] AND [Event End]<=[End Interval])

           

          and filtered only the W that matches that interval

          [Time]=[End Interval]

           

          Then the portion of W is calculated in this way

          IF [Event Start]>=[Start Interval] AND [Event End]<=[End Interval]

          THEN [W]

          ELSEIF [Event Start]>=[Start Interval] AND [Event Start]<=[End Interval]

          THEN [W]*DATEDIFF('minute',[Event Start],[End Interval])/120

          ELSEIF [Event End]>=[Start Interval] AND [Event End]<=[End Interval]

          THEN [W]*DATEDIFF('minute',[Start Interval],[Event End])/120

          END

           

          This link may have further information:

          Re: How to put the same entity into multiple category

          1 of 1 people found this helpful
          • 2. Re: Sum of measure in time intervals
            Lamia Z

            Hi Swaroop,

            thank you so much for your work. It gives me very valuable insight on the possibilities of Tableau, as I am still starting out using it.

            Sorry for not getting back to you earlier.