Average Order frequency MDX

# Average Order frequency MDX

• Thursday, January 24, 2013 3:18 AM

Can anyone think of a way to calculate the average number of days since the previous order was placed, for example, in Adventure Works?

Thanks! Josh Ash

• Edited by Thursday, January 24, 2013 3:44 AM
•

### All Replies

• Thursday, January 24, 2013 8:53 AM

you probably want to calculate the previous order of the same customer i guess - here is a little example:

```WITH

MEMBER [Measures].[DaysSinceLastOrder] AS (
IIf(
Count( -- check if there was a Last Order
NonEmpty(
{NULL:[Date].[Date].currentmember.prevmember},
[Measures].[Internet Order Count]) AS LastOrders) > 0,
Count(
Tail(
LastOrders,
1).item(0):[Date].[Date].currentmember),
NULL)
)

MEMBER [Measures].[AvgDaysSinceLastOrder] AS (
Avg(
EXISTING [Customer].[Customer].[Customer].members,
[Measures].[DaysSinceLastOrder])
)

SELECT
{
[Measures].[Internet Order Count],
[Measures].[DaysSinceLastOrder],
[Measures].[AvgDaysSinceLastOrder]
} ON 0,
NON EMPTY [Date].[Date].members ON 1
WHERE (
{[Customer].[Customer].&[12650],
[Customer].[Customer].&[12300],
[Customer].[Customer].&[13256]},
[Date].[Calendar Year].&[2007]
)
```

though, it will not perform very well so i would rather try to calculate it in ETL and add an extra column to the fact-table that holds the last DaysSinceLastOrder for the current customer
then you can simply do a COUNT and a SUM measure on that column and divide those values to get the average

hth,
gerhard

- www.pmOne.com -

• Thursday, January 24, 2013 8:54 AM

See this

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

• Marked As Answer by Monday, February 11, 2013 4:50 AM
•
• Thursday, January 31, 2013 10:00 PM

Thanks guys, I'm going to implement this in a couple of weeks so will come back with what I find, thanks for your help.

Thanks! Josh Ash

• Monday, February 11, 2013 4:52 AM

Hey guys

Thanks, the performance turned out to be a major issue, I added the column in the fact table to give the average days since last order, and then a member property on the customer dimension (via a column in the customer dimension table) to give the days since the last order..

Thanks! Josh Ash