Rank grouped by

[#1]

Hello,

I have the following table:

Category	Market	Brand	Model	Value	Desiderata
A	1	B1	M1	1	1
A	1	B1	M2	1	1
A	1	B2	M3	1	1
A	2	B1	M1	1	1
A	2	B2	M3	1	1
B	1	B2	M3	2	2
B	2	B1	M2	2	2
B	2	B2	M3	2	2
C	1	B1	M1	3	2
C	1	B1	M2	3	2
D	1	B1	M1	4	3
E	2	B2	M3	5	3
F	1	B2	M3	6	3
F	2	B2	M3	6	4
G	2	B1	M1	7	2
G	2	B2	M3	7	5

I would like to obtain the "Desiderata" column in which i rank "Value" grouping by "Market", "Brand" and "Model"

I tried to use the following:

Aggr(NODISTINCT Rank(TOTAL  -GlobalRank) ,Market,Brand,model)

but i have all null values.

Do you have any suggestions?

Thanks in advance.

[swuehl]

What is GlobalRank? Is this the Value field?

You should use aggregation functions in the aggr() expression whenever there is more than one possible Value for a Market, Brand, Model combination, which is the case here. So you need to decide how you want to aggregate the Value field values.

It's unclear to me how you derive the Desiderate values from Value field, so if you want to get more help, I believe you need to add more info about your setting and requirements.

[#3]

Yes I'm sorry, GlobalRank is the Value column.

The value column represent a global rank of category: A -> 1, B->2, etc.

What I would like to obtain is the rank of the Category partioned by Market, Brand and Model.

For this reason I do not have to aggregate the value column since it is a rank itself.

For example if I select a market 1, brand B1 and model m1 I would like to have the following:

Value     Desiderata

     1               1

     3               2

     4               3

Hope I've been clearer now.

Thanks for your help.

[swuehl · Accepted solution]

Maybe like

=Num(Aggr(NODISTINCT Rank(TOTAL  -Value) ,Market,Brand,Model,Value))

[#5]

Almost. I think we're close to the solution. The problem now  is that when I select market model and brand I can see the right rank, but when I deselect all, the rank changes. How is it possible to keep the same rank values even when all dimensions are not selected?

Thanks again.

[swuehl]

remove the TOTAL qualifier from the Rank() function.

[#7]

It works great!!!! Thanks!!!!