Using TOPCOUNT across one set to filter the results of another

Hey,

Quite new to MDX, hopefully i'm just being daft and there's a simple answer to this...

I'm trying to identify the top n members of a dimension, based on a particular dimension and date range (the current rolling week). That's straightforward enough. What I then want to do is to show measures for those top n members, across both the current rolling week and the previous rolling week.

Somewhat simplified, my code looks like:

select [Measures].[VALUE] on 0
    , ({[Date].[Rolling Month].&[1], [Date].[Rolling Month].&[2]}, TOPCOUNT(([Date].[Rolling Month].&[1],[Se Campaign].[Se Campaign Name].CHILDREN), 10, [Measures].[VALUE])) on 1
from [mycube]

This (quite rightly) tells me that i'm using the [Rolling Month] hierarchy more than once in the crossjoin. So I think what I want to do is to only return the [Se Campaign Name] from the TOPCOUNT set and not return the [Rolling Month]. I've tried to formulate this in different ways, using FILTER and can't get it to go.

Any help, hugely appreciated.

Well, topcount should do your work, as far as I understand your goal.

TOPCOUNT returns set inside the function, but only for the top tuples you select. Why you added rolling month 1 and 2 outside the TOPCOUNT function? Can you do it inside the TOPCOUNT function?

take a look http://msdn.microsoft.com/en-us/library/ms144792.aspx

Thanks for your reply.

The thing is, I want to get the topcount over rolling month 1, and then see the members of the second dimension mentioned in the topcount (secampaign) over rolling month 1 and 2. If I put rolling {rolling month 1 and 2} inside the topcount then I get the top 10 over both months.

I have just stumbled upon a solution though - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/ec4aa552-28d2-425d-8abf-bc7adc2f6ee6/.

(might also describe the problem better than I have!!!).

Using AGGREGATE in the numeric expression of topcount worked. So rather than ordering by the measure, order by the measure aggregated over rolling month 1. Then i'm not returning rolling month as a member of the set returned from topcount, so it's not groaning about the crossjoin. Result, happiness.

Thanks for your response though.

you maybe consider using UNION function.

Here is an article wich they whated to join 2 mdx queries.

http://sqlserverpedia.com/blog/sql-server-bloggers/joining-the-results-of-two-mdx-queries-together/