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]

Would you be able to share few rows of sample data and provide the expected output from the data provided?

[martynlloyd]

I have a similar requirement and I'm very interested to read the responses.

So far I have tried several combinations of Autonumberhash, Aggravations, and Rank.

Lets hope that someone out there has the solution.

Marty.

[avicnellyy]

I can't share the file due to sensitive info.

But see attached screen shots (sorry new to the forum so not sure how to attach in here.)

The first one is collapsed, just to show you that it is offering me 10 BBE dates.

The second is expanded to show what I mean that it is giving me all the pallets that related to those dates.

I just want it to give me 10 pallets only, not 10 dates.

Ignore the zeros - they won't be there when I set it up properly. So I'd like it to give me the A80012/085 pallet with 108 on, followed by A80012/101, A80012/421 - 436. But actually there are a lot more than that, as there are 20+ per date shown in the first screen shot.

Hope that helps?

[sunny_talwar]

I guess I understand the confidentiality constraints, but can you share some mocked up data and based on that mocked up data what is that you are looking to achieve? Doesn't have to be a full blown app, just need 30-40 rows of data and what you want to see from it?

[avicnellyy]

Can I put it into excel for you? Rather than a QV file?

[sunny_talwar]

Works for me.. but make sure to provide the expected output from the data you provide.

[avicnellyy]

So the NOW tab, is what I am currently getting.

The WANT tab is what I need - either just 10 pallets (ideal), or the minimum number of pallets (ie I get that I may need all of the ones showing in the second block because they have the same BBE).

I have three order numbers with the same product - they are feeding back the same pallets (that's ok!)

[sunny_talwar]

This seems like already aggregated data.. can you share the raw data?

[avicnellyy]

Yes - it is using the formula in my original request.

The raw data would be exactly the same except with more than 10 BBE dates, all with their own set of 10+ pallet references. So it wouldn't make a difference.

thanks