Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to make a pivot table with a top 5 by dimension values.
I used function aggr and rank with an expression like aggr(if(rank(myField)<6, Id),Mydimension)
but As you can see in attached QlikView file, I have more than 5 values by dimension value A.
My fourth column isn't a sum but a max value (I don't want use sum here).
It seems it's because duplicate values are ranked as the same rank value.
I have this:
Category | Rnk | Top 5 Id ranked by Amount Gap group by Category | Amount Gap |
A | 1 | 8 | 625 |
2-6 | 4 | 65 | |
6 | 65 | ||
3 | 65 | ||
9 | 65 | ||
10 | 65 | ||
B | 1 | 2 | 71 |
2 | 5 | 60 | |
3 | 4 | 50 | |
4 | 1 | 30 | |
5 | 3 | 20 | |
C | 1 | 3 | 71 |
2 | 2 | 65 | |
3 | 4 | 50 | |
4 | 1 | 30 | |
D | 1 | 9 | 84 |
2 | 3 | 65 | |
3 | 2 | 60 | |
4 | 1 | 50 | |
5 | 4 | 20 |
And I want this:
Category | Top 5 Id ranked by Amount Gap group by Category | Amount Gap |
A | 8 | 625 |
10 | 65 | |
9 | 65 | |
6 | 65 | |
4 | 65 | |
B | 2 | 71 |
5 | 60 | |
4 | 50 | |
1 | 30 | |
3 | 20 | |
C | 3 | 71 |
2 | 65 | |
4 | 50 | |
1 | 30 | |
D | 9 | 84 |
3 | 65 | |
2 | 60 | |
1 | 50 | |
4 | 20 |
Could you help me?
Thanks.
Hi,
Use the Rank() function with mode and format parameters to return a single value instead of a range:
Rank(Max(If(ConditionType = 'A' AND ConditionDate=vLastDate, Montant)), 4, 1)
That ", 4, 1" tells the Rank() function to return a single value for each row (4) and in case of coincidence, the lowest value (1). You will find further reading on the Rank() modes and formats in the Reference Manual. Some values may be repeated, but that's the way the Rank() function works at least in version 10 SR4 and version 11.
Hope that helps.
Miguel
Hi,
Use the Rank() function with mode and format parameters to return a single value instead of a range:
Rank(Max(If(ConditionType = 'A' AND ConditionDate=vLastDate, Montant)), 4, 1)
That ", 4, 1" tells the Rank() function to return a single value for each row (4) and in case of coincidence, the lowest value (1). You will find further reading on the Rank() modes and formats in the Reference Manual. Some values may be repeated, but that's the way the Rank() function works at least in version 10 SR4 and version 11.
Hope that helps.
Miguel
Thank you very much! It solve my problem.