Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

larisa_filonova
Contributor II

question about rank function

Hi all,

I have a question concerning rank function.

Say, I have two tables (see the file attached).

In the first table I can show TOP brands from position Number ‘X’ to position Number ‘Y’. X&Y values are user-defined and can be set in input boxes. E.g. a user can set TOP brands (from №11 to №20). This functionality is implemented by the means of calculated dimension in the table using aggr&rank function.

In the second table I show all possible brands always from position 1 to the maximal position.

In both tables I set Brands order using rank function: rank([Budget]).

As you can see the sort order in both tables is different. For instance HYUNDAI is defined as number 1 in the first table and as number 11 in the second table.

I need HYUNDAI to be number 11 in both tables.

How can I do it?

Thank you in advance,

Larisa Filonova

Tags (2)
1 Solution

Accepted Solutions
jpenuliar
Valued Contributor III

Re: question about rank function

Hi Larisa,

You have an extra sum() on the expression:

Sum(aggr(rank( sum( {$<Brands>} sum([Budget]) )), Brands))

This should work:

Sum(aggr(rank( sum( {$<Brands>}[Budget])), Brands))

9 Replies
jpenuliar
Valued Contributor III

Re: question about rank function

Hi Larisa,

Try the below as a 2nd expression you can rename as Rank:

Sum(aggr(rank( sum( {$<Brands>} Budget ), Brands))

larisa_filonova
Contributor II

Re: question about rank function

I tried to define the sort order in the first table using this function, but the result is not correct. All the brands have got sort number 26. See the file attached.

jpenuliar
Valued Contributor III

Re: question about rank function

Hi Larisa,

Can you attached your qvw file?

larisa_filonova
Contributor II

Re: question about rank function

Please, see the file attached.

In this example I want to see the followin rank order in the first table:

HYUNDAI must be number 11

...

CHEVROLET must be number 20

jpenuliar
Valued Contributor III

Re: question about rank function

Hi Larisa,

You have an extra sum() on the expression:

Sum(aggr(rank( sum( {$<Brands>} sum([Budget]) )), Brands))

This should work:

Sum(aggr(rank( sum( {$<Brands>}[Budget])), Brands))

larisa_filonova
Contributor II

Re: question about rank function

Thank you!

larisa_filonova
Contributor II

Re: question about rank function

There is another issue. Could you please help?

For instance, I have three brands with the same budget. Normaly for brands with the same value QlikView defines RankOrder by the means of the interval.

E.g.:

98-100   Brand 1

98-100   Brand 2

98-100   Brand 3

If I use your formula the result is defferent.

E.g.:

100   Brand 1

100   Brand 2

100   Brand 3


See the file attached.


Is it possible to tune the formula so that RankOrder is shown as interval (e.g. '98-100')?

jpenuliar
Valued Contributor III

Re: question about rank function

HI,

this might do the trick:

Aggr(Rank(Sum( {$<Brands>} [Budget] )), Brands)

larisa_filonova
Contributor II

Re: question about rank function

It does. Thank you!

Community Browser