6 Replies Latest reply on Jun 8, 2016 11:07 AM by Ivan Young

    Average of days from last transaction

    Hernan Estrin

      Hi team, I'm trying to get the AVG from days between current order date and the previous one.

       

      Customer NameOrder DatePrevious DateDifference from previous date
      Aaron Bergman19/Feb/11
      Aaron Bergman07/Mar/1119/Feb/1116
      Aaron Bergman11/Nov/1307/Mar/11980
      Aaron Hawkins22/Apr/11
      Aaron Hawkins13/May/1122/Apr/1121
      Aaron Hawkins25/Oct/1113/May/11165
      Aaron Hawkins31/Dec/1125/Oct/1167
      Aaron Hawkins27/Dec/1231/Dec/11362
      Aaron Hawkins21/Mar/1327/Dec/1284
      Aaron Hawkins19/Dec/1421/Mar/13638

       

       

      I've successfully calculated the numbers to be averaged as shown above. However when I try to place that number into a LOD expression, including order date (cause I want to remove it from rows) and averaging the result, I've got an error.

       

      {INCLUDE [Order Date]: MIN([Order Date])-[Previous Date] }

      "LOD expressions cannot contain table calculations or the ATTR function"

       

      How am I supposed to calculate the previous date (LOOKUP(MIN([Order Date]), -1) without using a Table calculation to be able to use the LOD expression?

       

      Workbook is attached

       

      Thanks in advance,

        • 1. Re: Average of days from last transaction
          Ashish Chaudhari

          HI Hernan,

           

          I have tried doing it and I have reached till below issue.

          "LOD expressions cannot contain table calculations or the ATTR function"

          I think we need to find some other logic for getting previous order date since Min() used in the that formula is the first level of aggregation which should be avoided in order to calculate the averages further.

           

          Tagging expert and friend. I think he can help you more on this. Mahfooj Khan

           

          Any advice/help is appreciated on this.

           

          Regards,

          Ashish Chaudhari

          • 2. Re: Average of days from last transaction
            Ivan Young

            Hi Hernan and Ashish,

            I think WINDOW_AVG will give you the average you are looking for.  Give it a try:  WINDOW_AVG([Difference from previous date]), I believe the records with null [Difference from previous date] won't be included.  The average seems to be 188.9 days between orders.  Let me know if you have any questions.

             

            Regards,

            Ivan

             

             

             

            Or if you are looking at Days between orders by customer you will need to modify compute on the Avg calc.

             

            • 3. Re: Average of days from last transaction
              Hernan Estrin

              Hi Ashish & Ivan, thank you for your selfless help.

               

              I think Ashish is closer to what I need. Ivan: although with your suggestion I'm able to compute the Average Days between orders by customer, the problem is that I want to remove the order date and previous date from the view and include any other Field such as customer name, product name, etc. and get the average.

               

               

              (When Order date is removed)

               

              If I do that right now I've got an error, since both fields need to be included in order to make the average calculation. That's why I'd originally decided to use an INCLUDE LOD expression to bring always the order date (despite not being displayed). But when I do that I've got the error: "LOD expressions cannot contain table calculations or the ATTR function"

               

              I would appreciate you could continue helping me

               

              Best,

              • 4. Re: Average of days from last transaction
                Ivan Young

                Hi Hernan,

                I got it, it appears I didn't read your post very thoroughly.  I'm not sure you can get your lookup for previous day to work without having [Order Date] in the view.  Attached is an example that returns the average days per customer on a single line.

                 

                What I did was create 3 LODs:  Min Date by Customer, Max Date by Customer, # or Orders by Customer -1(Not counting the first Order).  I then created a datediff between min and max dates which I then used as the numerator for the average calculation.  Check out the attached and let me know if you have any questions. (Example without Lookup) worksheet.  To account for the multiple dimensions you wish to analyze you could use a parameter to swap the fields if you want to consolidate your views.  Let me know if you have any questions.

                 

                Best,
                Ivan

                2 of 2 people found this helpful
                • 5. Re: Average of days from last transaction
                  Hernan Estrin

                  Muchas gracias Thank you very much!

                   

                  That was what I was looking for. I should have noticed that the sum of difference between order date and previous order date is equal to the difference between max and min order date.

                  • 6. Re: Average of days from last transaction
                    Ivan Young

                    De nada Hernan,  Happy to help. Using lookup seemed like the most straightforward solution but it's somewhat limiting as it required date in the view.