Pivot table - Duplicated values - Top 5 by Dimension values

[#1]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

Thank you very much! It solve my problem.