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

# Average of days from last transaction

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

 Customer Name Order Date Previous Date Difference from previous date Aaron Bergman 19/Feb/11 Aaron Bergman 07/Mar/11 19/Feb/11 16 Aaron Bergman 11/Nov/13 07/Mar/11 980 Aaron Hawkins 22/Apr/11 Aaron Hawkins 13/May/11 22/Apr/11 21 Aaron Hawkins 25/Oct/11 13/May/11 165 Aaron Hawkins 31/Dec/11 25/Oct/11 67 Aaron Hawkins 27/Dec/12 31/Dec/11 362 Aaron Hawkins 21/Mar/13 27/Dec/12 84 Aaron Hawkins 19/Dec/14 21/Mar/13 638

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

• ###### 1. Re: Average of days from last transaction

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

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

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

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

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

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.