topic Re: Rank Over D in QlikView

Rank Over D

Anonymous — Thu, 14 Sep 2017 17:53:33 GMT

I am trying to create two separate rankings in expressions - one that ranks a value across all IDs and another that ranks the same value across a subsection of IDs. This is how I would expect it to display:

ID	Value	Category	Overall Rank	Category Rank
A1	10	A	1	1
A2	5	A	3	2
A3	8	B	2	1
A4	3	B	5	3
A5	4	B	4	2
A6	1	A	6	3

I can write an expression to get the overall rank, but am having no luck with ranking across a subset of the IDs. Here is the formula I'm using for both:

Overall:

Rank(TOTAL Sum({<Status={'ACTIVE'}>} Value),1, 1)

Aggr(Rank(TOTAL Sum({<Status={'ACTIVE'}>} Value),1, 1), Category)

Anyone have experience with this?

Re: Rank Over D

sunny_talwar — Thu, 14 Sep 2017 17:56:38 GMT

For Category, try this

Aggr(Rank(Sum({<Status={'ACTIVE'}>} Value), 1, 1), Category, ID)

Re: Rank Over D

its_anandrjs — Thu, 14 Sep 2017 18:30:40 GMT

For OverAll try this

=Aggr(Rank(Sum( {<Status={'ACTIVE'}>} Value)),Category,ID)

For Category Rank

=Aggr(Rank(Sum( {<Status={'ACTIVE'}>} Value), 1, 1), Category, ID)

Re: Rank Over D

Anonymous — Sun, 17 Sep 2017 13:59:12 GMT

Thank you for the help. I got it to work using the following:

Overall:

=Aggr(Rank(Sum( {<[Status]={'ACTIVE'}>} Value), 1, 1), ID)

Category:

=Aggr(Rank(Sum( {<[Status]={'ACTIVE'}>} Value), 1, 1),Category, ID)