topic Help on Rank function in QlikView

Help on Rank function

Sun, 29 Jul 2012 20:04:38 GMT

Hi,

Can somebody help me with the rank function in the below scenario.

I have a crosstable with the following columns:

code	MeasureType	Value
ABC	Turnover	3000
ABC	Marketshare	3000
ABC	Ranking	3000
PQR	Turnover	2500
PQR	Marketshare	2500
PQR	Ranking	2500
XYZ	Turnover	3200
XYZ	Marketshare	3200
XYZ	Ranking	3200

I am looking to provide the following information for code ABC:

MeasureType	Value
Turnover	3000
MarketShare	34.48 (3000/(3000+2500+3200)*100
Ranking	2 (3200 is 1st, 3000 is 2nd and 2500 is 3rd)

With Measure type as dimension i am able to acheive Turnover and Marketshare. but not able to acheive the Ranking column. can somebody help me with this.

Thanks in advance

Re: Help on Rank function

swuehl — Sun, 29 Jul 2012 20:30:50 GMT

I think it could be just

rank(sum(Value))

I used code and MeasureType as dimensions and a pick(match()..) combination as expression to create the measures per MeasureType. Please see also attached.

Re: Help on Rank function

Sat, 04 Aug 2012 11:10:49 GMT

thanks swuehl....it work for me...but cant figure out how you made the dimension (MeasureType) as columns...

Re: Help on Rank function

swuehl — Sat, 04 Aug 2012 11:38:33 GMT

It's a pivot table with the dimension pivoted to the top:

Click on "MeasureType" in the graph and move your mouse around while keeping button pressed. You'll see a blue line indicating the new position of the dimension.