Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Average Order frequency MDX

Answered Average Order frequency MDX

  • Thursday, January 24, 2013 3:18 AM
     
     
    Sign In to Vote
    0

    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 Josh Ashwood Thursday, January 24, 2013 3:44 AM
    •  
     

All Replies

  • Thursday, January 24, 2013 8:53 AM
    Answerer
     
     Answered Has Code
    Sign In to Vote
    0

    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
FROM [Adventure Works]
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
     
     Answered
    Sign In to Vote
    0

    See this

    http://consultingblogs.emc.com/christianwade/archive/2011/01/15/days-since-last-event-tsql-vs-mdx-vs-dax.aspx

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

    • Marked As Answer by Josh Ashwood Monday, February 11, 2013 4:50 AM
    •  
     
  • Thursday, January 31, 2013 10:00 PM
     
     
    Sign In to Vote
    0
    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
     
     
    Sign In to Vote
    0

    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