2 Replies Latest reply on Oct 20, 2017 6:25 AM by Daniel Santos

    Sum items across series based on different time series (but withing the same intervals)

    Daniel Santos

      Hello,

       

      I've been trying to figure out around this problem but I'm not being able to. I have a table with these fields:

       

      Removed Station | Removal Hour | Returned Station | Return Hour

      Station A         05:00          Station B          05:30

      Station A         06:00          Station B          06:15

      Station C         07:00          Station A          07:05

      Station B         07:15          Station C          07:23

      ....

       

      I have in one sheet the number of removals over time during a day ("Removal Hour" pill as column, "Removed Station" and counting of "Removed Station" as rows), and a similar sheet with the data regarding the returns.

      I'd like to have a sheet with the number of total interactions with the stations, basically the sum of removals and returns, over time as well.

       

      So let's say that what I have looks like this:

       

      Removals

      Station Name 8 9 10 11 12 13

      Station A    2 3 4  6  10 0

       

      Returns

      Station Name 8 9 10 11 12 13

      Station A    1 0 3  2  5  1

       

      Totals

      Station Name 8 9 10 11 12 13

      Station A    3 3 7  8  15 1

       

      The complication factor, in this case, would be to sum the removals and returns using two different time series. Since I have these two series, would I be able to make a calculated field "TimeDiff", and plot removals based on removal time and plot returns based on removal time + TimeDiff?

       

      It feels like it shouldn't be so hard. In excel, let's say, I could just make a third sheet with the sum of the two previous ones, but I can't reference an item from another sheet in Tableau.

       

      Thank you all in advance.

       

      Best regards, Daniel

        • 1. Re: Sum items across series based on different time series (but withing the same intervals)
          Jim Dehner

          Hi

          You need to create a continuous time series that is a frame work for you 2 different time lines - see the link below for a step by stem in how to do it

           

           

          Showing Records That Fall Within a Period of Time | Tableau Software

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Sum items across series based on different time series (but withing the same intervals)
            Daniel Santos

            Hello Jim,

             

             

            Thanks for the answer.

             

             

            Unfortunately I don't think that helps me. The article you linked describes how I'd account the amount of active trips from one station to the other during a specified interval.

             

             

            For example:

             

             

            Removed Station | Removal Hour | Returned Station | Return Hour

            Station A         05:00          Station B          06:30

            Station A         06:00          Station B          08:15

            Station C         07:00          Station A          07:05

            Station B         07:15          Station C          07:23

             

             

            Would return me the following (making some adjusts to the example provided):

             

             

            Station Name | 5 | 6 | 7 | 8 | 9

            Station A      1   2   1   1   0

            Station B      0   0   0   0   0

            Station C      0   0   1   0   0

             

             

            But what I really want to have is the amount of removals+returns that occurr within a given interval. The result I expect is:

             

             

            Station Name | 5 | 6 | 7 | 8 | 9

            Station A      1   1   1   0   0

            Station B      0   1   1   1   0

            Station C      0   0   2   0   0

             

             

            In a little bit more detail:

             

             

            Station A: There's 1 removal at 5:00, one removal at 6:00 and one return at 7:05.

            Station B: There's 1 return at 6:30, 1 removal at 7:15 and 1 return at 8:15.

            Station C: There's 1 removal at 7:00 and 1 return at 7:23 (summing 2 interactions from 7 to 8).

             

             

            As you can see, if I take in consideration the whole duration of the trip I'd have misleading results.

             

             

            Thanks again, Daniel