topic Re: Rank Function Help in QlikView

Rank Function Help

alvinford — Wed, 12 Apr 2017 12:31:17 GMT

Hi All ,

I have the below table , the requirement is to rank the UserName based on the sales value and display the first three UserNames as in the output shown below. Please help to get this requirement.

UserName	ShopName	SalesValue
X	ABC	100
Y	ABC	90
X	ABCD	100
Y	ABCD	90
X	ABCE	90
Y	ABCF	100
Z	ABC	80
Z	ABCG	90
V	AAA	80
Z	ABCF	100
V	ABC	80
U	ABB	70
V	ABCD	80
U	ABD	80
V	ABCF	20

Output

Rank	UserName	ShopName	SalesValue
1	X	ABC	100
		ABCD	100
		ABCE	90
2	Y	ABC	90
		ABCD	90
		ABCF	100
3	Z	ABC	80
		ABCG	90
		ABCF	100

Thanks & Regards,

Alvin

Re: Rank Function Help

MK_QSL — Wed, 12 Apr 2017 12:40:56 GMT

Like this?

Create a pivot table

Dimension

=Aggr(IF(Rank(SUM(Total<UserName>SalesValue))<=3,Rank(SUM(Total<UserName>SalesValue))),UserName)

Tick Suppress When Value is Null

2) UserName

3) ShopName

Expression

SUM(SalesValue)

Re: Rank Function Help

tresB — Wed, 12 Apr 2017 12:44:46 GMT

May be this?

Expression: =Aggr(If(Rank(Sum(SalesValue))<4, Rank(Sum(SalesValue))),UserName)

Re: Rank Function Help

alvinford — Sat, 15 Apr 2017 09:42:43 GMT

Hi Tresesco,

Thank you for your reply. Its Working fine. Have other requirement on the top it. How can I display Top 2 ShopNames for the Top three ranks as I have more than one ShopNames

The Output expected is as below

Rank	UserName	ShopName	SalesValue
1	X	ABC	100
1	X	ABCD	100
2	Y	ABCD	90
2	Y	ABCF	100
3	Z	ABCG	90
3	Z	ABCF	100

Thanks & Regards,

Alvin

Re: Rank Function Help

tresB — Mon, 17 Apr 2017 07:29:19 GMT

Use the expression like below:

If(Rank(Sum({<UserName={"=Rank(Sum(SalesValue))<4"}>}SalesValue),4)<3, Sum(SalesValue))

Re: Rank Function Help

alvinford — Wed, 19 Apr 2017 02:41:52 GMT

Hi Tresesco,

Thank You Very much .. it worked for me ..