3 Replies Latest reply on Sep 13, 2013 6:19 AM by Alex Kerin

    Subtract date ranges from records over time

    Randin Lambrecht

      Hello Tableau community,


      While seemingly simple in theory, this has me stumped! I'm looking to find the average days between a customer's transactions recorded on a transactional 'ChangeDate'. I'm sure it has something to do with Lookup, but was unsure how to implement it in this scenario.


      Datediff('day',Lookup([ChangeDate], 1), [ChangeDate])   - gives the aggregation error and I'm not sure if the 1 offset will even work when the entries are so spread out.


      The arrows on the picture indicate the datediff I'm trying to find.


      Thanks for your help, guys & gals



        • 1. Re: Subtract date ranges from records over time
          Alex Kerin

          You will have to deal with sorting and partitioning of the table calc, but first to get the calc right, each [date] needs to be attr([date])

          1 of 1 people found this helpful
          • 2. Re: Subtract date ranges from records over time
            Randin Lambrecht

            Hey Alex, thanks for the tip, it definitely pointed me in the right direction. Here's the solution I came up with:


            1. Start by indexing all of the transactions - Index()


            2. Create a calc field :


                 Lookup(attr([ChangeDate]), 1)


            3. Create another calc field that does the datediff (in hours) and stops when moving on to the next records


                 If lookup([Calculation2],1) < [Calculation2] then

                 (Datediff('minute', (attr([ChangeDate])),NOW() ))/60

                 else(Datediff('minute', (attr([ChangeDate])),[LookupTest1]))/60



            4. Just for good measure, one more calc field to better illustrate the separate records when dropped on the color shelf:

                 If lookup([Calculation2],1) < [Calculation2] then


                 else 0



            Here's the result (red is the end of a record)

            [edit: status = different transactions on an account]



            From here we can easily find averages and graph it out.