Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nikhilgarg
Specialist II
Specialist II

Rank Function

If we want to get the Top 20 products , we will use following set analysis:

Sum({<Product = {"=rank(sum(Sales), 4)<= 20"}>} Sales)

But what does dis 4 in {"=rank(sum(Sales), 4)<= 20"} signifies?? Please tell me....

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

From the manual

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment

and returns the ranking of the current row within the segment. For bitmap charts, the current column

segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a

straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment

is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions,

the current column segment will include only rows with the same values as the current row in all dimension

columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an

integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled

as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default) If all ranks within the sharing group fall on the low side of the middle value

of the entire ranking, all rows get the lowest rank within the sharing group.

If all ranks within the sharing group fall on the high side of the middle value

of the entire ranking, all rows get the highest rank within the sharing group.

If ranks within the sharing group span over the middle value of the entire ranking,

all rows get the value corresponding to the average of the top and bottom

ranking in the entire column segment.

1 Lowest rank on all rows.

2 Average rank on all rows.

3 Highest rank on all rows.

4 Lowest rank on first row, then incremented by one for each row.

The third parameter format specifies the text representation of the function result.

format

0 (default) Low value&' - '&high value on all rows (e.g. 3 - 4).

1 Low value on all rows.

2 Low value on first row, blank on the following rows.

The order of rows for mode 4 and format 2 is determined by the sort order of the chart dimensions.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

From the manual

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment

and returns the ranking of the current row within the segment. For bitmap charts, the current column

segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a

straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment

is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions,

the current column segment will include only rows with the same values as the current row in all dimension

columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an

integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled

as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default) If all ranks within the sharing group fall on the low side of the middle value

of the entire ranking, all rows get the lowest rank within the sharing group.

If all ranks within the sharing group fall on the high side of the middle value

of the entire ranking, all rows get the highest rank within the sharing group.

If ranks within the sharing group span over the middle value of the entire ranking,

all rows get the value corresponding to the average of the top and bottom

ranking in the entire column segment.

1 Lowest rank on all rows.

2 Average rank on all rows.

3 Highest rank on all rows.

4 Lowest rank on first row, then incremented by one for each row.

The third parameter format specifies the text representation of the function result.

format

0 (default) Low value&' - '&high value on all rows (e.g. 3 - 4).

1 Low value on all rows.

2 Low value on first row, blank on the following rows.

The order of rows for mode 4 and format 2 is determined by the sort order of the chart dimensions.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Hi Nikhil,

Rank function format is as below:

rank([ total ] expression [ , mode [, format ] ])


Here, the second parameter mode specifies the number representation of the function result.

mode

  

0 (default)If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group.
If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group.
If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1Lowest rank on all rows.
2Average rank on all rows.
3Highest rank on all rows.
4Lowest rank on first row, then incremented by one for each row.

Please refer the attached example to know the difference.

Hope this helps.

Thanks

Sabal

nikhilgarg
Specialist II
Specialist II
Author

HEy , Thans for your reply...

nikhilgarg
Specialist II
Specialist II
Author

HEy , Thans for your reply...