I've struggled with this one for a while. Any help is greatly
appreciated.



I'm creating a report (relevant dimensions are product, time, customer)
that needs to look like the following:





_________| product0 | product1 | product2



last month | value0 | value1 | value2



last 2 months | value3 | value4 | value5



last 3 months | value6 | value7 | value8







The "values" are the number of customers that spent more than $0 on the
corresponding products in the LAST 12 MONTHS, and have underlying data
based on the months on the left. So for example, value0 is the number
of customers that spent more than $0 on product0 in the LAST 12 MONTHS
and have been active (underlying data exists for them) in the last
month. value3 is the number of customers that spent more than $0 on
product0 in the LAST 12 MONTHS and have been active (underlying data
exists for them) in the last 2 months. etc. etc. I have a [sales]
measure that corresponds to the money spent.



Once again, any insights are greatly appreciated.


--
Posted via http://dbforums.com

Here is an MDX query for the Foodmart Sales cube, which produces similar
results (only last month and last 2 month shown for brevity). Note that
filtering for customers with > $0 in the last 12 months really slows
down the query. Not sure why it's needed, since they must have activity
anyway in the past month/2 months/3 months:

'{ClosingPeriod([Time].[Month],[Time].[1997])}'
Set [Last2MonthSet] as 'LastPeriods(2,[LastMonthSet].Item(0))'
member [Measures].[LastMonth] as
'NonEmptyCrossJoin(Filter([Customers].[Name].Members,

Sum(LastPeriods(12,[LastMonthSet].Item(0)),

([Measures].[Store Sales],[Product].CurrentMember)) > 0),

[LastMonthSet],{[Product].CurrentMember},1).Count'
member [Measures].[Last2Months] as
'NonEmptyCrossJoin(Filter([Customers].[Name].Members,

Sum(LastPeriods(12,[LastMonthSet].Item(0)),

([Measures].[Store Sales],[Product].CurrentMember)) > 0),

[Last2MonthSet],{[Product].CurrentMember},1).Count'

Select [Product].[All Products].[Drink].[Beverages].Children on Columns,
{[Measures].[LastMonth], [Measures].[Last2Months]} on Rows
From Sales
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!