Get previous sale for a customer's sale?

SQL Server Analysis Services

tee_user5


If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004 (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblishedStick out tongue)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

Top

Re: Get previous sale for a customer's sale?

Bryan C. Smith


So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly :-)

Bryan






Top

Re: Get previous sale for a customer's sale?

tee_user5

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].CurrentMember.MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue ,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])





Top

Re: Get previous sale for a customer's sale?

Bryan C. Smith

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results

Thanks,
Bryan





Top