Finding the Rank of special value

[Obstacel]

Hi,

I have a problem with finding the rank of a special value.

I have a create a pivot table and I want to show the rank of a special product inside the dimension "Region" (see example below).

I have tried different things but I can't find a solution to show the right rank. So could you please help me?

Unfortunately loading the rank inside the script is not a good solution because of loosing the dynamic options.

Thank you very much.

Example for the raw data:

Country	Region	Product	Value
Germany	North	A	100
Germany	North	B	50
Germany	North	C	20
Germany	East	A	70
Germany	East	B	60
Germany	East	C	90
Germany	South	A	20
Germany	South	B	10
Germany	South	C	80
Germany	West	A	60
Germany	West	B	40
Germany	West	C	50

Pivot Table:

Country	Region	Value	Rank Product C
Germany	North	170	3
	East	220	1
	South	110	1
	West	150	2

[anushree1]

Not sure if this is what you are looking for..

Please check the attached.

[Obstacel]

Thank your for your answer.

Unfortunately I'm using only QlikView and so I'm not able to open your attached file.

[tresesco]

How are East and South regions having same rank, i.e -1 ?

[Obstacel]

Because "Product C" has in both regions the highest rank. In region "North" Product C has the 3rd highest rank and in "West" 2nd highest rank in comparison to the other products.

[tresesco · Accepted solution]

Got it. It's a good one. 🙂

You can try something like:

how powerful aggr() is ! 😲

Aggr(IF(Product='C',Rank( Aggr(Value, Country,Region,Product))),Country,Region,Product) 

[Obstacel]

Wow! Amazing...It's exactly the solution I was looking for.

Thank you very much...