topic Re: Min & Max 10 values in pivot table in QlikView

Min & Max 10 values in pivot table

L_Hop — Tue, 16 Jan 2018 20:03:43 GMT

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.

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Tue, 16 Jan 2018 22:01:17 GMT

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

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Tue, 16 Jan 2018 22:09:35 GMT

For the bottom ones:

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

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Tue, 16 Jan 2018 22:13:53 GMT

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

Re: Min & Max 10 values in pivot table

L_Hop — Wed, 17 Jan 2018 07:34:53 GMT

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;

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Wed, 17 Jan 2018 12:26:40 GMT

If your question was solved, please close this thread.

Cheers

Re: Min & Max 10 values in pivot table

L_Hop — Wed, 17 Jan 2018 14:59:41 GMT

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

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Wed, 17 Jan 2018 15:07:20 GMT

This one:

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

Re: Min & Max 10 values in pivot table

Thiago_Justen_ — Thu, 18 Jan 2018 02:34:05 GMT

Let's put all together:

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