MDX query count of customers who purchased in prior period and this period

Hi there

I'm looking for help with an mdx query that will tell me how many of my customers are returning to purchase each period and thus by default how many existing customers haven't purchased this period.

So in English I want;

Count of distinct customers who were active in this period and were also active in prior period.

Or in T-SQL something like this

select count(*) from (Select distinct customer_id from fact_table where date = current period and exists (select distinct customer_id from fact_table where date = prior period))

I have a measure called distinct customer count and a dimension called DIm Customer which currently doesn't have customer_id as a level in any hierarchy, although it is the key field in this dimension, as I don't want to go to this level of detail if I can help it, not sure if this makes the above impossible.

Any ideas would be much appreciated.

Regards,

Derek

Hi Derek,

Here's a sample query for Adventure Works, which computes the count of customers with Internet Sales in both the current and prior Calendar periods - it's based on the count of common members being difference between the sum of member counts in each set and the distinct count of members across 2 sets:

>>

With Member [Measures].[ContinuingCustomers] as

Sum(LastPeriods(2, [Date].[Calendar].CurrentMember),

[Measures].[Customer Count]) -

Aggregate(LastPeriods(2, [Date].[Calendar].CurrentMember),

[Measures].[Customer Count])

select {[Measures].[Customer Count],

[Measures].[ContinuingCustomers]} on 0,

{[Date].[Calendar Year].&[2002]:

[Date].[Calendar Year].&[2004]} on 1

from [Adventure Works]

where [Product].[Subcategory].&[1]

--

Customer Count ContinuingCustomers
CY 2002 615 0
CY 2003 1,961 267
CY 2004 2,094 288

>>

|||

Hi Deepak

Thanks very much that is exactly what I wanted, one thing though when I use this measure my cube response slows dramatically and my cpu use spikes, any idea why?

Cheers,

Derek

|||Derek, I suspect that it has to do with the performance of the dstinct count measure, but I'm not sure. Doing a SQL Profiler trace, with all query events selected, could help identify where the time is spent.|||

Thanks again Deepak

I'll take a look at that, I may have to end up doing some of the work for this query in the data source view.

Cheers,

Derek