function rank

[syukyo_zhu]

Hi,

I use a function rank in my application Qlikview,  for example to calculate the sale’s rank for the salesman in a table.  So I can get a result like this.

rank	salesman	amount
1	sale1	600
2	sale2	500
3	sale3	400
4	sale4	300
5	sale5	200
6	sale6	100

Now I want to compare amount of sales just for sale1, sale2 and sale4 in a pivot table, but I need show  their ranks calculated in my first table as below

rank	salesman	product1	product2	product3
1	sale1	200	200	200
2	sale2	200	150	150
4	sale4	150	150	100

The result of sale’s Rank isn’t saved in a file

How can I do it.

Thanks

[jonathandienst]

You might need to calculate your rank values in the load script. That way, they are not calculated in the context of a specific table.

If that's not possible, then you would use a calculated dimension or amend the expression so that it calculates the rank as per the first result, but then excludes ranks other than those you would like see.

If you provide a small sample qvw with some representative data, it will be possible to give more specific help.

[effinty2112 · Accepted solution]

Hi Xia,

Try this pivot table with a calculate dimension:

=Aggr(Rank(Sum({1}Sales)),salesman)	salesman	Product	product1	product2	product3
1	sale1		200	200	200
2	sale2		200	150	150
4	sale4		150	150	100

Kind regards

Andrew

[syukyo_zhu]

Hi andrew,

It works.

Thanks

[effinty2112]

You're very welcome!