MDX query to aggregate distinct count

Hi there

I'm after some help with an mdx query, I'm using SQL 2005 analysis services and I want to create a query which compares the most recent three months with three months 1 month prior. The measure i want to compare is a distinct count so can't use sum to combine. i attempted to use (for the current 3 months);

aggregate([Dim Date].[Calendar Year].CurrentMember.Lag(3) :

[Dim Date].[Calendar Year].CurrentMember,

[Measures].[Active Player Count])

but got an error that I can't use aggregate on a calculated member, the measure Active Player Count is a standard measure that has distinct count measure as it's aggregation.

Hi,

It's not clear from the MDX fragment here what exactly you're trying to do, but here's an example using Adventure Works which does something similar:

WITH MEMBER MEASURES.LASTTHREEMONTHS AS

AGGREGATE([Date].[Calendar].CURRENTMEMBER : [Date].[Calendar].CURRENTMEMBER.LAG(2),[Measures].[Customer Count])

MEMBER MEASURES.LASTTHREEMONTHSONEPRIOR AS

AGGREGATE([Date].[Calendar].CURRENTMEMBER.PREVMEMBER : [Date].[Calendar].CURRENTMEMBER.LAG(3),[Measures].[Customer Count])

SELECT {[Measures].[Customer Count], MEASURES.LASTTHREEMONTHS,MEASURES.LASTTHREEMONTHSONEPRIOR} ON 0,

[Date].[Calendar].[Calendar Quarter].&[2003]&[4].CHILDREN

ON 1

FROM

[Adventure Works]

HTH,

Chris

|||

thanks Chris

Looks like I had the same query but had the lag around the wrong way , I had the lag (3) in the first part of the equation rather than the second.

Derek