topic Re: Dense Rank in QlikView in QlikView

Dense Rank in QlikView

Fri, 19 Dec 2014 21:10:12 GMT

Hi All,

How to implement oracle data base dense_rank function in qlikview chart.

Example:

Employee	Salary	Rank	Dense_Rank
A	10000	1	1
B	8000	2	2
C	8000	3	2
D	5000	4	3

Appreciate your help on this!!!

Thanks,

Shekar.

Re: Dense Rank in QlikView

jjordaan — Fri, 19 Dec 2014 21:33:56 GMT

Try this Num(Rank(Sum(Salary), 1))

Instead of 1 you can use differnet options

Mode 0 (default) Rows 2 and 3 share ranking but are clearly on the lower half of the total ranking. Their number representation is therefore rounded downwards to 2. Rows 4 and 5 share ranking too, but fall just above the middle of the ranking table. Therefore they get a number representation of the average of the first and the last rank in the column ((1+8)/2=4.5). This mode is especially useful when you want to use Visual Cues to mark the data ranking highest and lowest within a group.

Mode 1 In both cases the lower ranking figure within the group is used, i.e. 2 for rows 2 and 3, 4 for rows 4 and 5.

Mode 2 In both cases the average of the low and high ranking within the group is used, i.e. 2.5 ((2+3)/2) for rows 2 and 3, 4.5 ((4+5)/2) for rows 4 and 5.

Mode 3 In both cases the higher ranking figure within the group is used, i.e. 3 for rows 2 and 3, 5 for rows 4 and 5.

Mode 4 Each row is assigned its own distinct numeric value. The order within groups sharing a ranking is determined by the sort order of the chart's dimensions.

Hope this helps

Re: Dense Rank in QlikView

anbu1984 — Mon, 22 Dec 2014 09:05:33 GMT

Dimension: Employee

Expression: If(RowNo() = 1,1,If(Sum(Salary) = Above(Sum(Salary)),Above(Column(1)),Above(Column(1))+1))

Re: Dense Rank in QlikView

Tue, 09 Aug 2016 06:57:45 GMT

Hi Shekar,

I tried this using "above" function.

1. Expression based on which I want Rank :

CL= sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2

2. Rank : just applied rank on above expression. I want rank 1 if sum is less, so applied negative sign also.

Rank= rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2)

3. Rank ‘-’ is to check whether that rank containing ‘-’ or not

if(index(rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2) ,'-',1)>0 ,1,0)

4. Above ‘-’ is to check previous row rank containing ‘-’ or not

if( index(above(Column(2)),'-',1)>0,1,0)

5. In Rank & above ‘-’ , combining expression 3 & 4.

=if(
if(index(rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2) ,'-',1)>0 ,1,0)=1 and
if( index(above(Column(2)),'-',1)>0,1,0)=1,
1,0)

6. First Row: to check whether this row is First or not

if(above(Column(2))>=0,0,1)

7. Updated Rank : combining expressions in 5 & 6

if(
if(above(Column(2))>=0,0,1)=1 ,
if(if(index(rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2) ,'-',1)>0 ,1,0)=1,1,rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2)),
if((if(if(index(rank(-sum({<ST_Year={'2016'}>}EXTRA_FUEL)*2.2) ,'-',1)>0 ,1,0)=1 and if( index(above(Column(2)),'-',1)>0,1,0)=1,1,0))=1,above(column(7)),above(column(7))+1)
)