4 Replies Latest reply on Jan 28, 2013 2:54 AM by Amarendu Samal

    Calculating the diffrence between two rows and two related column in a table.

    Amarendu Samal

      Hi,

       

      Got stuck in a situation......

      Here i need  the difference between OpenDTm of the 2nd row with the CloseDTm of first row .

      and here every two row is the data of a single transaction.

       

      Here i need to find the difference between  consecutive transaction of a particular user.

       

      User         OpenDtm                  CloseDtm

       

      0123  |2009-11-30 17:21:00|2009-12-02 11:30:00|

      0123  |2009-12-14 19:30:00|2009-12-19 15:25:00|

       

      0133  |2010-01-13 21:34:00|2010-01-15 15:30:00|

      0133  |2010-01-23 23:00:00|2010-01-26 14:40:00|

       

      0133  |2010-01-23 23:00:00|2010-01-26 14:40:00|

      0133  |2010-01-29 01:45:00|2010-02-01 17:00:00|

       

      0148  |2009-10-31 10:57:00|2009-11-05 11:25:00|

      0148  |2009-11-26 09:02:00|2009-12-01 17:52:00|

       

      0149  |2010-12-08 05:00:00|2010-12-17 18:47:00|

      0149  |2010-12-24 23:36:00|2011-01-18 17:30:00|

       

      0149  |2012-01-11 17:35:00|2012-01-19 18:10:00|

      0149  |2012-01-23 20:30:00|2012-01-25 15:58:00|

       

      0149  |2012-05-17 15:25:00|2012-06-13 19:50:00|

      0149  |2012-06-25 14:13:00|2012-07-02 18:40:00|

       

      0170  |2011-12-11 17:52:00|2011-12-13 21:15:00|

      0170  |2012-01-10 22:02:00|2012-01-13 16:35:00|

       

      0172  |2010-11-05 01:20:00|2010-11-11 11:40:00|

      0172  |2010-11-16 05:18:00|2010-11-19 20:32:00|

       

      0172  |2011-01-03 09:38:00|2011-01-07 14:45:00|

      0172  |2011-01-15 17:18:00|2011-01-19 18:51:00|

       

      0172  |2011-01-15 17:18:00|2011-01-19 18:51:00|

      0172  |2011-01-28 20:32:00|2011-01-31 20:15:00|

       

       

      Thanks

      Amarendu

        • 1. Re: Calculating the diffrence between two rows and two related column in a table.
          Joshua Milligan

          Amarendu,

           

          It would be immensely helpful if the data had another field that would link the related records together (see example below).  Then you could just get the min open date and max close date for each set.

           

          I was able to come up with a solution using your data.  I've attached a workbook so you can see.  It uses some table calculations to determine which records should go together and it makes the assumption that the order of open date determines the sets of rows.  Feel free to ask if you have any questions.

           

          Joshua

           

           

           

          What would be nice:

          Case #UserOpenDtmCloseDtm
          101232009-11-30 17:21:002009-12-02 11:30:00
          101232009-12-14 19:30:002009-12-19 15:25:00
          201332010-01-13 21:34:002010-01-15 15:30:00
          201332010-01-23 23:00:002010-01-26 14:40:00
          301332010-01-23 23:00:002010-01-26 14:40:00
          301332010-01-29 01:45:002010-02-01 17:00:00
          401492010-12-08 05:00:002010-12-17 18:47:00
          401492010-12-24 23:36:002011-01-18 17:30:00
          501492012-01-11 17:35:002012-01-19 18:10:00
          501492012-01-23 20:30:002012-01-25 15:58:00
          1 of 1 people found this helpful
          • 2. Re: Calculating the diffrence between two rows and two related column in a table.
            Amarendu Samal

            Hi Joshua,

             

             

            Thanks for your reply , it seems like it will help me a lot.

             

            But i have doubt that how did you compute the Index() in measures field.

            Please explain me how did you build it.

             

            Thanks in advance

            Amarendu

            • 3. Re: Calculating the diffrence between two rows and two related column in a table.
              Joshua Milligan

              Amarendu,

               

              I used a few table calculations to solve your issue.  Ideally another field in the data would greatly simplify the solution, as table calculations can be quite complex.  But if that isn't possible, then I can't think of another way to solve it.  If you are not familiar with table calculations, you might want to take a look at some knowledge base articles like this one:http://kb.tableausoftware.com/articles/knowledgebase/table-calculations

               

              Index() is a built in function in Tableau.  It is a table calculation which allows us to give each mark or cell in the table an index (e.g. 1, 2, 3, 4...).  What I did was to index each row, but restart the index at each user.  So then we have something like this:

               

              User         OpenDtm                 CloseDtm | Index

               

              0133  |2010-01-23 23:00:00|2010-01-26 14:40:00| 1

              0133  |2010-01-29 01:45:00|2010-02-01 17:00:00| 2

              0148  |2009-10-31 10:57:00|2009-11-05 11:25:00| 1

              0148  |2009-11-26 09:02:00|2009-12-01 17:52:00| 2

              0149  |2010-12-08 05:00:00|2010-12-17 18:47:00| 1

              0149  |2010-12-24 23:36:00|2011-01-18 17:30:00| 2

              0149  |2012-01-11 17:35:00|2012-01-19 18:10:00| 3

              0149  |2012-01-23 20:30:00|2012-01-25 15:58:00| 4

               

              So to match each pair of rows, I will take each even row and match it with the previous (2 will be matched with 1, 4 with 3, etc...)

              I can accomplish this with another calculation that does a mod on the Index().  In this case, if Index() % 2 = 0 then I know it's an even row and I can get the value of the previous row's start date by using the Lookup() table calculation.

              Again, the best solution would be to have data that identified the pairs of rows.  Then your solution would be far simpler.  But hopefully the above explanation is helpful if  you don't have any other options.

               

              Joshua

              • 4. Re: Calculating the diffrence between two rows and two related column in a table.
                Amarendu Samal

                Thanks a lot....

                 

                 

                 

                Case #UserOpenDtmCloseDtm
                101232009-11-30 17:21:002009-12-02 11:30:00
                101232009-12-14 19:30:002009-12-19 15:25:00
                201332010-01-13 21:34:002010-01-15 15:30:00
                201332010-01-23 23:00:002010-01-26 14:40:00
                301332010-01-23 23:00:002010-01-26 14:40:00
                301332010-01-29 01:45:002010-02-01 17:00:00
                401492010-12-08 05:00:002010-12-17 18:47:00
                401492010-12-24 23:36:002011-01-18 17:30:00
                501492012-01-11 17:35:002012-01-19 18:10:00
                501492012-01-23 20:30:002012-01-25 15:58:00

                 

                 

                As you have mention the Case# column in this table, is there any way i could put this in my table.

                 

                The above process suggested by you was great , just want to know that can I some how put this Case# column in my tableau workbook then my whole problem would be solved.

                 

                Thanks again....

                 

                Amarendu