Rank Over D

[Anonymous]

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?

[sunny_talwar]

For Category, try this

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

[its_anandrjs · Accepted solution]

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)

[Anonymous]

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)