9 Replies Latest reply on Nov 19, 2016 3:12 PM by Simon Runc

    Find the average time between Nth orders.

    L L

      OK I got a hard one today, what I am trying to do is find the average time between the 1st order to the 2nd order, 2nd order to the 3rd order,3rd order to the 4th order, etc for all orders in the database. So this is the average time a customer takes between orders. I can do this easy with just finding the 1st to 2nd but I am not sure how to find it for all orders till there is any more repeat orders. Any insight will be helpful. I have attached a sample workbook to apply this solution to.

        • 1. Re: Find the average time between Nth orders.
          Joe Oppelt

          I'm not exactly sure what you are looking to compare, but in the attached I show how to use the LOOKUP() function to look at the previous value.

          So you can probably use LOOKUP to get a previous order date and do a DATEDIFF or AVG(DATEDIFF()) to do what you want.

          • 2. Re: Find the average time between Nth orders.
            Norbert Maijoor

            HiJoe Oppelt


            What I understand is that L would like to have the average days between orders for a customer.


            I am able to calculate the "next date": LOOKUP(ATTR([Order Date]),1) and define the delta: DATEDIFF('day',attr([Order Date]),[Next Date])


            a. AVG(DATEDIFF('day',attr([Order Date]),[Next Date])) gives me the "well-known" error.......and cannot be further aggregated

            b. {fixed [Customer Name]:sum([Delta per Customer])} gives me the "well-known" error......and cannot be further aggregated


            Let me know if you know "a way out".  Your insights are appreciated.


            • 3. Re: Find the average time between Nth orders.
              Joe Oppelt

              LOOKUP is a table calc, so you can't do AVG() on that.


              But you can do WINDOW_AVG() on table calcs.  So in my initial reply when I suggested AVG(DATEDIFF()) I was mistaken.


              So if you have a string of the DATEDIFFs, you'll do a WINDOW_AVG on that.

              • 4. Re: Find the average time between Nth orders.
                L L

                So this is kinda what I meant but maybe I can clarify, What I want to do is take all of the customers that have made a second order and get the difference in days then average all of them. then do the same for 2nd to 3rd and 4th to 5th and 5th to 6th, etc. So it will look something like this:

                Repeat order | Average days Between orders

                1 order          | 40 days

                2 order          | 32 days

                3 order          | 103 days

                4 order          | 567 days

                5 order          | 765 days

                6 order          | 10345 days



                This will be aggregated for all customers and orders.

                • 5. Re: Find the average time between Nth orders.
                  Simon Runc

                  hi L,


                  So yes this is a very (very!) tricky problem in Tableau...to get the Average time between orders isn't too bad (although does need a few complicated Table Calculations), however when we want to further partition this into time-between 1st and 2nd, 3rd and 4th...Table Calculation aren't going to work (well not with-out it getting stupidly complicated!), as we are unable to use a Table Calculation result as a partition...btw here is the idea to allow this if you want to vote it up https://community.tableau.com/ideas/1194 .


                  LoDs can do this (and I have done this in the attached), but we need to create a separate LoD expression for 1st to 2nd Orders, 2nd to 3rd...etc. so it's very scalable, or dynamic. In Superstore the Max number of orders by one person is 16, so we'd need 32 calculations...and then when new data comes in and someone orders for the 17th time we need to create another 2!!


                  The best way (IMHO) to achieve this to do use some CustomSQL to re-shape the data, and allow us to do this fairly simply...Do you have the scope to re-shape the data? and if so what is the source of your data (Excel, SQL...?). If you have let me know and I can show you how.


                  So for the LoD method (I've only done the orders 1 to 4)...


                  First I create an LoD (against customer) which brings back (for each row for each customer) their 1st, 2nd, 3rd...purchase

                  [1st Order Date]

                  { FIXED [Customer Name] : MIN([Order Date])}


                  [2nd Order Date]

                  { FIXED [Customer Name]: MIN(IIF([Order Date]>[1st Order Date],[Order Date],NULL))}


                  [3rd Order Date]

                  { FIXED [Customer Name]: MIN(IIF([Order Date]>[2nd Order Date],[Order Date],NULL))}


                  [4th Order Date]

                  { FIXED [Customer Name]: MIN(IIF([Order Date]>[3rd Order Date],[Order Date],NULL))}


                  Once I have these I can create calculations, which give me the time-difference between each one

                  [1st to 2nd Order Date]

                  DATEDIFF('day', [1st Order Date], [2nd Order Date])


                  [2nd to 3rd Order Date]

                  DATEDIFF('day', [2nd Order Date], [3rd Order Date])


                  [3rd to 4th Order Date]

                  DATEDIFF('day', [3rd Order Date], [4th Order Date])


                  and I can then bring these onto the pane, aggregated as Averages



                  Let me know on the Reshaping the data, else you are going to need a lot of calculations...and as LoDs these won't scale that well.


                  btw the tab "Only Between Any Orders" is just me playing with using Table Calcs, to work out the median time between any orders (and then putting these in a bin)...but doesn't show this difference broken down by 1st, 2nd, 3rd...

                  • 6. Re: Find the average time between Nth orders.
                    Yuriy Fal

                    Hi aLL,


                    Simon is absolutely right about the perceived complexity of this case in Tableau.

                    It would be much simpler if one could have Rank of Orders been materialised --

                    hard-coded directly in the datasource (either manually or via window function in Custom SQL).


                    As a complimentary -- if the table output is not an obligation --

                    the results could be visualised as a Reference Line.

                    This is just another level of Table Calculations, after all.


                    Please find the attached.





                    PS As a bonus, a RefLine could be a Median, too.

                    3 of 3 people found this helpful
                    • 7. Re: Find the average time between Nth orders.
                      Simon Runc

                      Very nice, Yuri


                      I always forget you can force one more Table Calc with a reference line (and partitioned across headers, even if they are Table Calcs). Personally, I think seeing the range, as well as the average, is actually much better (more richness of insight)


                      • 8. Re: Find the average time between Nth orders.
                        L L

                        This is awesome guys, I have implemented Simons solution only displaying up to 10 orders. Simon can you attach your wookbook? I would like to see how you did the above screenshot.

                        • 9. Re: Find the average time between Nth orders.
                          Simon Runc

                          Excellent...Glad you stopped at 10!!


                          So I would highly recommend this session from the recent TC16...


                          Sign Up | Tableau Conference Live


                          Bethany basically solves exactly your issue here. She uses some custom SQL (although she shows a nifty trick, to get Tableau to write the query using the performance recorder, and just copying and pasting the SQL). In that way she joins the data back on itself, but off-set by one. So each row now contains the Order Date and the Previous Order date (on the same row). In order to perform this join, she created a rank column (in the SQL), and in the second table did Rank - 1, and then joined Rank to Rank -1 (this creates the off-set). Now she then went on to use this to create all sorts of customer cohorts (not really using the Rank column again). For your case, this is the dimension which is each customers Nth Order.


                          Once you have the data like this, apart from doing the DATEDIFF on the Order Date and Previous Order date, you'll need no other calcs and can simply just drag AVG of Datediff and Rank onto the canvas.


                          I've attached the workbook...I just changed Yuri's lines to circles, Jittered them with Random(), added Box and Whiskers + Median Reference Line, and switched the Axis