Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Pivot table - Duplicated values - Top 5 by Dimension values

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:

CategoryRnkTop 5 Id ranked by Amount Gap group by CategoryAmount Gap
A18625
2-6465
665
365
965
1065
B1271
2560
3450
4130
5320
C1371
2265
3450
4130
D1984
2365
3260
4150
5420


And I want this:

CategoryTop 5 Id ranked by Amount Gap group by CategoryAmount Gap
A8625
1065
965
665
465
B271
560
450
130
320
C371
265
450
130
D984
365
260
150
420

Could you help me?

Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot table - Duplicated values - Top 5 by Dimension values

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

2 Replies
MVP
MVP

Re: Pivot table - Duplicated values - Top 5 by Dimension values

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

Not applicable

Pivot table - Duplicated values - Top 5 by Dimension values

Thank you very much! It solve my problem.

Community Browser