Selective Aggr

[Anonymous]

Hi!

I have 2 tables; one containing all my products, and one containing all the purchaseorders, when products are bought.

In the product table I have a return quantity, representing the quantity I want to return. My goal is for each product to return, to get the list of PO I must reference to cover the quantity.

To better show what I try to do let`s take this sample data:

Product:

load * inline [

ProductCode, QtyReturn

1 , 500

2 , 900

3 , 200

4 , 300

];

PurchaseOrder:

load * inline [

PO_Num, ProductCode, QtyBought, PO_Date

2234, 1 , 200, 2018-06-03

2235, 2 , 300,  2018-06-02

2234, 3 , 100,  2018-06-03

2234 ,4 , 500,  2018-06-03

3214 , 1 , 100, 2018-06-02

4564 , 1 , 200, 2018-05-22

3245, 2 , 2500, 2018-05-22

5645, 3, 200, 2018-05-22

4344, 1 , 200, 2018-04-03

];

With this formula I am able to get a list of all PO for each product and all quantities

=aggr(concat(PO_Num,', ', -PO_Date ),ProductCode)

Which for product 1 is : 2234, 3214, 4564, 4344

and

=aggr(concat(QtyBought,', ', -PO_Date ),ProductCode)

200, 100, 200, 200

What I really want is to get only the PurchaseOrder numbers until I reach the amount to return. QtyReturn = 500 for product 1.

So, for product 1, it would be : 2234, 3214, 4564 instead of 2234, 3214, 4564, 4344.

I tried many combination of Aggr, Sum, Rank ... But can`t seem to put my finger on the solution ...

Here is the last thing I tried, which give me just 2234 ... not even close.

= concat( IF( aggr ( rank( sum(QtyBought )),ProductCode)<=QtyReturn, PO_Num)  , ';', -PO_Date )

Thanks for your help, hope I was able to explain clairly what I am trying to achieve.

[Gysbert_Wassenaar]

You'll have to sort the aggr. That's complicated but, have a look at this blog post: Recipe for a Pareto Analysis – Revisited

[sunny_talwar · Accepted solution]

Try this

=Concat(Aggr(If(QtyReturn >= RangeSum(Above(Sum(QtyBought), 0, RowNo())), PO_Num), ProductCode, PO_Date), ', ', -PO_Date)

[Anonymous]

I will go read the blog post to learn more ...

[Anonymous]

That works ... thanks ... Will check it until I fully understand it.

[Anonymous]

By checking more, it does not check for the dates correctly. Trying to get the most recent dates first. But if they are not already sorted, that does not seem to work. That is why I had the  minus sign in front of PO_Date.

[sunny_talwar]

Yes, if the dates are not sorted from the script... you have two options

1) If you have qv12 or above... you can use this

The sortable Aggr function is finally here!

=Concat(Aggr(If(QtyReturn >= RangeSum(Above(Sum(QtyBought), 0, RowNo())), PO_Num), ProductCode, (PO_Date, (NUMERIC))), ', ', -PO_Date)

2) Sort PO_Date in the script

[Anonymous]

I am still at version 11 .. Will use option 2 ...