topic Re: Rank grouped by in QlikView

Rank grouped by

Sun, 28 Feb 2016 19:42:15 GMT

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.

Re: Rank grouped by

swuehl — Sun, 28 Feb 2016 20:08:34 GMT

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.

Re: Rank grouped by

Sun, 28 Feb 2016 20:39:17 GMT

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.

Re: Rank grouped by

swuehl — Sun, 28 Feb 2016 20:53:21 GMT

Maybe like

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

Re: Rank grouped by

Sun, 28 Feb 2016 21:47:32 GMT

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.

Re: Rank grouped by

swuehl — Sun, 28 Feb 2016 21:58:41 GMT

remove the TOTAL qualifier from the Rank() function.

Re: Rank grouped by

Sun, 28 Feb 2016 22:09:20 GMT

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

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

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

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