"loyal customers" dynamic set

Question

Hi,

I've the following scenario:

- Fact table: with field (SK_Date, SK_Customer, SK_Project, InvoiceAmount)

- Dimensions: Dim_Date, Dim_Customer, Dim_Project)

Dim_Date has a category (year - month).

I need to create a dynamic set with customers whom we have invoiced more than 2 disctint projects in last 60 months since the month.currentmember. Obviously, if we are navigating in Feb 2012, loyal customers are different to Jan 2012.

Is it posible? I supose that I could use filter and dynamic set in MDX, but is dificult for me to construt this sentence.

Thanks,

Juan Perez

Answer 1

Hi Juan,

Could you give us an example to explain distinct projects for one customer? With the explain, you should be able to build a measure used for the distinct count calculation. Then, the dynamic set could be something like this -

Create Dynamic Set CurrentCube.[Loyal Customers]

As

Filter([Customer].[Customer].members,Aggregate({[Date].[Hierarchy Month].currentmember.lag(60):[Date].[Hierarchy Month].currentmember},[Measures].[Project Count]) > 2)

Hope this helpfully,

Regards,
Jerry

Answer 2

This works fine.

Thanks.