Min & Max 10 values in pivot table

[L_Hop]

Hello everyone,

I am trying to get max and min 10 shipment quantities in table chart. For max values I have tried "=Max(Sum(aggr([Shipment Quantity],Date)))".

But I am not able to get the exact values.

Please suggest me any solution if anyone of you gone through the same problem.

Suppose I have below data structure:     

Shipment Quantity	Vessel Name	Date	Product
5050	Vessel A	12.12.2017	Fuel
7500	Vessel B	13.12.2017	Fuel
6250	Vessel C	14.12.2017	Cement
8605	Vessel A	15.12.2017	Cement
15047	Vessel B	16.12.2017	Fuel
5000	Vessel C	17.12.2017	Cement
7540	Vessel A	18.12.2017	Cement
6250	Vessel B	19.12.2017	Cement
8905	Vessel C	20.12.2017	Cement
23047	Vessel A	21.12.2017	Cement
15862	Vessel B	22.12.2017	Cement
14789	Vessel C	23.12.2017	Fuel
85479	Vessel A	24.12.2017	Cement
5874	Vessel B	25.12.2017	Cement
32145	Vessel C	26.12.2017	Fuel
98214	Vessel A	27.12.2017	Cement
52148	Vessel B	28.12.2017	Cement
78654	Vessel C	29.12.2017	Fuel

What I want is, just only see the the max and min 10 shipment quantities and other related informations on the table.

Max 10 shipment quantity sheet should be something like:

Shipment Quantity	Vessel Name	Date	Product
98214	Vessel A	27.12.2017	Cement
85479	Vessel A	24.12.2017	Cement
78654	Vessel C	29.12.2017	Fuel
52148	Vessel B	28.12.2017	Cement
32145	Vessel C	26.12.2017	Fuel
23047	Vessel A	21.12.2017	Cement
15862	Vessel B	22.12.2017	Cement
15047	Vessel B	16.12.2017	Fuel
14789	Vessel C	23.12.2017	Fuel
8905	Vessel C	20.12.2017	Cement

Please suggest any appropriate solution...

Thanks.

OY

[Thiago_Justen_]

Using this script:

DATA:

Load * Inline [

ShipmentQuantity,VesselName,Date,Product

5050, Vessel A, 12.12.2017, Fuel

7500, Vessel B, 13.12.2017, Fuel

6250, Vessel C, 14.12.2017, Cement

8605, Vessel A, 15.12.2017, Cement

15047, Vessel B, 16.12.2017, Fuel

5000, Vessel C, 17.12.2017, Cement

7540, Vessel A, 18.12.2017, Cement

6250, Vessel B, 19.12.2017, Cement

8905, Vessel C, 20.12.2017, Cement

23047, Vessel A, 21.12.2017, Cement

15862, Vessel B, 22.12.2017, Cement

14789, Vessel C, 23.12.2017, Fuel

85479, Vessel A, 24.12.2017, Cement

5874, Vessel B, 25.12.2017, Cement

32145, Vessel C, 26.12.2017, Fuel

98214, Vessel A, 27.12.2017, Cement

52148, Vessel B, 28.12.2017, Cement

78654, Vessel C, 29.12.2017, Fuel

]

I had this result:

My Top 10 formula is:

Only({<ShipmentQuantity={'=rank(ShipmentQuantity)<=10'}>}ShipmentQuantity)

Cheers

[Thiago_Justen_]

For the bottom ones:

Only({<ShipmentQuantity={'=rank(ShipmentQuantity,4)>=10'}>}ShipmentQuantity)

[Thiago_Justen_]

Note that for the bottom ones I used the rank with an additional argument: it was 4. This means that I would the "Lowest rank on first row, then incremented by one for each row.".

If you have any doubts concerning rank function, please check this out:

Rank - chart function ‒ QlikView

[L_Hop]

Hello thiago.justen

Thank you for your time and effort.

Top 10 formula has been working.

However, low 10 formula is not working.

When I have used low 10 formula, I am getting the all the results except top 10 result.

Do you have any other suggestion?

regards;

OY

[Thiago_Justen_]

If your question was solved, please close this thread.

Cheers

[L_Hop]

Still I could not find a solution to get low 10 results.

[Thiago_Justen_]

This one:

Only({<ShipmentQuantity={'=rank(ShipmentQuantity,4)>=10'}>}ShipmentQuantity)

[Thiago_Justen_]

Let's put all together:

Only({<ShipmentQuantity={'=rank(ShipmentQuantity)<=10'} or ShipmentQuantity={'=rank(ShipmentQuantity,4)>=10'}>}ShipmentQuantity)