6 Replies Latest reply on Nov 14, 2018 7:19 AM by William Hauser

    Average time between dates

    William Hauser

      Suppose I have data structured just like the superstore data.  Each row is an order, with a date.  Some people have several 'orders' (i.e. rows), some have just one.  I'd like to see the average time between orders.  

       

      Using the superstore data as an example, I tried the following calc. without success:

       

      window_avg(

           datediff('day',lookup(min([Order Date]),-1,min([Order Date]) )

           )

       

      When I put Customer Name out on the rows and the calculated field on the labels everyone has the same value  (.2399) which, I think, means that people have .24 days between orders, on average.  This should be different for each person and missing or zero for those with just one order.  It's like Tableau is not partitioning the way I want it to - I want the average time between dates calculated at the person level, not overall.  The other thing that worries me, looking at the table calc. above is that it looks like I'm taking just the first earliest order date compared to the one before it - what about people with 3 or more order dates?  Is this calc going to ignore all those orders?  I tried using '0' in the first call to lookup  and '1' in the second to address this but get that annoying error about all fields must be aggregate or constant when using table calcs.

        • 1. Re: Average time between dates
          Don Wise

          Hi William,

          If you adjust your Table Calc settings to the following, I think you'll get better results.  If yes, please mark this response as correct. Thx, Don

          Screen Shot 2018-11-12 at 1.10.59 PM.png

          1 of 1 people found this helpful
          • 2. Re: Average time between dates
            William Hauser

            Hi Don,

            This is very close and very helpful.  Part of the trick is that the date needs to be set to "exact date" which then makes it continuous and then it must be set back to discrete.  I find that if I just put date on the view I get a confusing mess of date parts to work with in the table calc.  The table as you have it works well enough but I'd rather not have the rows repeat for each person. 

             

            If I can get the time since last call in one column and the average in another (or as a subheader) that'd be great. I know if I take the "window_avg" out of the calc I get the time since last call.  I can put this out in a table but when I try to use the "totals" function to get the average for each person as a subheader it does not seem to work.  I attached a screen cap of what I'm trying to do.  I also added a color flag to identify orders less than 1 week apart.

             

            Other issues - can I get the overall average somehow? 

             

            time between example.PNG

            • 3. Re: Average time between dates
              Don Wise

              Hi William,

              Please see below and attached 2018.3 workbook. I mimicked what I believe you're looking for? Yes, when doing Table Calc's they can be somewhat rigid in how things are displayed; so for example, if I were to remove 'Customer Name', then there'd have to be something else to partition off of.  So in this case, but your actual use-case might be different, the Customer Name has to be kept in order for the Table Calc to partition correctly.  I did add in the Days Between and a color flag.  Hope it helps! Thx, Don

              Screen Shot 2018-11-13 at 12.29.07 PM.png

              • 4. Re: Average time between dates
                William Hauser

                This works great.  Thank you.  Is there anyway to get an overall average out of this table?  It'd also be nice to do some grouping as well to see what percentage had a second order within a week, month, year, etc.

                • 5. Re: Average time between dates
                  Don Wise

                  Hi William,

                   

                  I've attached a revised 2018.3 workbook with the additional calc which is a WINDOW AVERAGE across all rows of the AVERAGE BETWEEN DAYS calc.  Hope it helps..

                   

                  For the grouping, I'd say make that a new question on the forums as you get deeper into what you'd like to do...!  Thx, Don

                   

                  Screen Shot 2018-11-13 at 3.27.21 PM.png

                  • 6. Re: Average time between dates
                    William Hauser

                    Thanks again Don, this is helpful!