TM1 Forum

Hi:

I have a requirement to build a report that features rankings as follows:

Top 10 Salespeople
Top 20 "
Top 30 "
Top 31-100 "
Top 101-350 "
Remainder

I think the first three can be achieved by using MDX in the active form, however I am not sure if it can create the others so I am wondering, before I attempt it, is there another solution I should be looking at that would work for all?

I understand that you cannot do this in Excel when working with an Active Form - for example assigning a number to each Salesperson and then using SUMIFS - or can you? ( it is my understanding that if you change the data structure the Excel formulas will not hold? Any guidance would be greatly appreciated.

Thank you in advance.

I would suggest researching the MDX keyword EXCEPT, on the (pseudocode) lines of
<Top 100 Salespersons> EXCEPT <Top 30 Salespersons>

Hi

Another thing to try is HEAD and TAIL. HEAD takes the first n elements of a set, and TAIL takes the last n elements of a set.

The following is an example on a time dim

{TAIL({HEAD({[zTD_Effective_Mth].[e_Effective Mths_Base]},13
)},3)}

So to get eg the top 30 and the top 31-100

Get the Top 100 Ranked and then take Head 30, and Tail 70 of that.

Regards

Paul Simon