topic Re: Rank Function in QlikView

Rank Function

Wed, 05 Sep 2012 04:57:31 GMT

Hi everyone,

Can anyone explain rank function with an example?

Many thanks,

Murali

Re: Rank Function

Anonymous — Fri, 24 Jan 2014 11:46:19 GMT

Hi murali,

Brief explaination of rank function:

Create a calculated dimension to display top N values

Make sure that you check “Suppress when value is NULL” for the calculated dimension.

= aggr(if(Rank(sum(product))<= 5 ,name),name)

Above expression creates below list of 5 products

Thanks praneetha

Re: Rank Function

Anonymous — Fri, 24 Jan 2014 11:51:37 GMT

anant

Re: Rank Function

Fri, 24 Jan 2014 12:02:48 GMT

see attachment

Re: Rank Function

MK_QSL — Fri, 24 Jan 2014 12:06:16 GMT

Pls check enclosed file...

Re: Rank Function

Fri, 24 Jan 2014 13:36:31 GMT

Rank function:

It is just like giving Serial number to our record set based by some field.

Suppose if we have as below:

Product , Quantity

Shoes, 10

Shoes,20,

Shirt,1

Shirt, 13

Shirt, 14

Pant,12

Then Rank(Product) will give us row number based on different product values

Product , Quantity, rank(Product)

Shoes, 10,1

Shoes,20,,2

Shirt,1,3

Shirt, 13,1

Shirt, 14,2

Pant,12,1

Thanks,

Angad

Re: Re: Rank Function

Tue, 29 Apr 2014 16:19:08 GMT

This was a very helpful discussion! Wondering how to apply the Rank function based on a certain dimension.

I'm displaying this in a trellis chart by Industry, showing the month over month % change over time. But, I'd like to:

1. Rank Industries based on their % change MOM from the prior month to current. In other words, highest % change for Mar.

2. Display only the top x number of Industries based on this ranking.

I've figured out how to Rank(pct_chg_MOM) to rank these by industry, and also how to Rank(total pct_chg_MOM) to get overall ranks, but am not clear how to accomplish the above.

One option I thought of is to use rank(total pct_chg_MOM) but then look only at Mar. But again, stuck on how to look only at Mar.

My data looks like this:

Industry	Month	pct_chg_MOM
ABC	Jan	-.08
ABC	Feb	-.03
ABC	Mar	1.0
DEF	Jan	0.9
DEF	Feb	1.1
DEF	Mar	0.4

Re: Rank Function

Wed, 30 Apr 2014 06:16:09 GMT

Hello Tina,

Is it possible for you to share a sample app for this with expected results? I believe in expression "pct_chg_MOM" you are using set analysis.

I did the similar thing, by using set analysis, and taking max month number from selection and then comparing it with previous month.

You must have a unique number, to identify which month is going.

For this we use MonthSeq calculated in script while creating master calendar.

Num(Month(TempDate)) + (YearStd-1)*12 AS MonthSeq

Hope this was helpful.

Thanks,

Angad