topic Re: Need to calculate RANK in QlikView

Need to calculate RANK

Wed, 21 Nov 2012 15:22:31 GMT

Hi,

I have a table that looks something like this:

I have the bar chart and it displays the percentage of each city

I need to calculate the RANK in a TEXT BOX for "LONDON"

It should look like

London - Rank - 2 in a TEXT BOX.

Please help me out. I am new and struggling with this.

its_anandrjs — Wed, 21 Nov 2012 15:27:33 GMT

Write a script in expression like

Rank( Sum( (Numerator/Denominator)*100 ), 0, 1 )

HTH

Regards,

Anand

Wed, 21 Nov 2012 15:33:30 GMT

I created a text box and wrote the expression you mentioned. It just says 1 in the TEXT BOX.

its_anandrjs — Wed, 21 Nov 2012 15:41:41 GMT

In a text box write this

=only({<City={"=rank(sum({<City={'London'}>}(Numerator/Denominator)*100))=1"}>}

aggr(rank(sum((Numerator/Denominator)*100)),City))

HTH

Regards,

Anand

Anonymous — Wed, 21 Nov 2012 15:55:17 GMT

Anand,

Nice solution. Two suggestions:

1. I think the set could be simple {<City='London'>}

2. To avoid rank like '2 - 5'0 when there are multiple Cities with the same amount, use num(rank(...))

=only({<City={'London'}>} aggr(num(rank(sum((Numerator/Denominator)*100))),City))

Regards,

Michael

Wed, 21 Nov 2012 15:55:51 GMT

Sorry Anand. It doesnt work. Just gives as output

Wed, 21 Nov 2012 15:57:26 GMT

Sorry Anand. It doesnt work. Just gives a minus sign in the text box when I apply it.

Wed, 21 Nov 2012 16:16:03 GMT

Thanks Anand and Michael. I made some Changes and it worked.

The changes I have made was to add the MOde to the expression.