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.
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.
Or if you are looking at Days between orders by customer you will need to modify compute on the Avg calc.
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
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.
Test Herno.twbx 1.2 MB
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.
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.