How to show 10 values only for a dimension

[avicnellyy]

Hi

I am trying to create a temporary pick list for our warehouse whilst we change systems. My dimensions are Supplier / Order Number / Product / BBE / Pallet ref / Location (there are others but not that affect this I don't think).

Expression is Quantity (on each specific pallet).

We have a lot of pallets for the same product, so I want it to just suggest 10 pallets for them to choose from based on the oldest BBE.

I am currently using this calculated dimension in a pivot table:

=if(aggr(rank(sum(quantity)),best_before_date)<=10,best_before_date)

But this pulls back the first 10 BBE dates, and shows all the pallets with those BBE dates (which is still too many).

If I change BBE in the above formula to 'Pallet ref' then it gives me 10 pallets with the highest quantity, which I don't want.

How do I get it to only show 10 pallet references (or the minimum required - ie 2 BBE dates, first has 5 pallets second may show 7)

[sunny_talwar]

okay checking

[sunny_talwar]

Most of the Pallet seem to have a Qty of 108. How would you Rank all the 108s?

[avicnellyy]

I want to rank by the BBE - which is a dimension, not an expression

[sunny_talwar]

You want to Rank by BBE, but you want to Rank, sum(Qunatity), right? What you are ranking has the same value. I feel like I am not 100% sure I understand your issue. I can continue to help, but I hope someone else who understand your issue might be able to help you better here.