2 Replies Latest reply on Jul 10, 2017 7:15 PM by Irina Zholudeva

    Number of Days between Orders

    Irina Zholudeva

      Hello, everybody! I want to calculate number of days between orders and the average number of day for each Client (Card Number) in my data set. I found example and followed it but I cannot solve the problem that it takes into account the first date of the current client and the last date of the previous client. Could you possibly help me to do it and to calculated the average number of day for each Client? TWBX is attached.

        • 1. Re: Number of Days between Orders
          Michel Caissie

          Irina,

           

          Check in the attached,

           

          The main thing you are missing is to properly set the computing on your table calculations.

           

          If you click on the table calculation pills little triangle, then click Edit Table Calculation,  select  Specific Dimensions,  select both Dimensions  and  Restarting every CardNumber.

          This way the date differences will compute only on dates from the same CardNumber.

           

          Next I changed  [Days  between orders] for

          if isnull([nextorderdate]) then 0 else DATEDIFF('day',ATTR([ChequeDate (DAY)]),[nextorderdate]) end

          in order to get a 0 instead of a null  on the first date order of a customer.

           

          I compute the Avg  with

          WINDOW_SUM( [Days between orders] ) / ([Nb of orders] - 1 )

           

          where Nb of orders is

          WINDOW_COUNT( ATTR( [ChequeDate (DAY)] ) )

           

          Pay attention,  AvgDays is a nested table calculation,  so you have to apply the computing on  every Nested Calculations.

           

          On Sheet234,  I add those two measures values. I keep the same dimensions on the Rows but I hide the dates (right-click the pill , unselect Show Header).

          And I add a filter in order to keep a single row per customer.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Number of Days between Orders
            Irina Zholudeva

            Michel, thank you very much for such a detailed answer.