Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Dimension: Employee
Expression: If(RowNo() = 1,1,If(Sum(Salary) = Above(Sum(Salary)),Above(Column(1)),Above(Column(1))+1))
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)
)