How to find Min & Max value row wise in straight table

[Vasu2021]

Hi,

I need to create a straight table with two extra fields like "Qty for Min Release" and "Qty for Max Release".

Here is the sample data.

Orderdata:
LOAD * INLINE
[
SalesOrder, ReleaseNum, Deliverydate, Quantity
55001, 4, 20210411, 300
55001, 4, 20210412, 200
55001, 7, 20210411, 50
55001,  7, 20210412, 150
55001, 7, 20210413, 400
55002, 8, 20210411, 1300
55002, 8, 20210412, 1200
55002, 9, 20210411, 150
55002, 9, 20210412, 800
55002, 9, 20210415, 1600
];

I expect the output like this. For each combination of SalesOrder & Deliverydate, take the Quantity for Min ReleaseNum and Max ReleaseNum. For instance, In the first row, 300 qty belongs to ReleaseNum 4 which is min release and 50 qty belongs to ReleaseNum 7 which is max release. 

In the fourth row, 1300 qty belongs to ReleaseNum 8 which is min release and 150 qty belongs to ReleaseNum 9 which is max release. 

To get these results, I tried with the below expressions. But I could not succeed.

=Aggr(max({<ReleaseNum={$(=max(ReleaseNum))}>}[Quantity]
),[SalesOrder],Deliverydate) -- Qty for Max ReleaseNum

=Aggr(max({<ReleaseNum={$(=min(ReleaseNum))}>}[Quantity]
),[SalesOrder],Deliverydate) --  Qty for Max ReleaseNum

Any help is much appreciated.

Thanks in Advance.

[rubenmarin]

Hi, for Min you can use:

If(Count(ReleaseNum)>1,FirstSortedValue(Quantity,ReleaseNum))

And for max:

FirstSortedValue(Quantity,-ReleaseNum)

[Vasu2021]

Hi, Thanks for your reply.

I tried it but your logic is not working in all cases. I have added two extra entries to the existing data.

Orderdata:
LOAD * INLINE
[
SalesOrder, Releasedate, ReleaseNum, Deliverydate, Quantity
55001, 20210409, 4, 20210411, 300
55001, 20210409, 4, 20210412, 200
55001, 20210409, 4, 20210415, 250
55001, 20210429, 7, 20210411, 50
55001, 20210429, 7, 20210412, 150
55001, 20210429, 7, 20210413, 400
55002, 20210409, 8, 20210411, 1300
55002, 20210409, 8, 20210412, 1200
55002, 20210409, 8, 20210413, 460
55002, 20210429, 9, 20210411, 150
55002, 20210429, 9, 20210412, 800
55002, 20210429, 9, 20210415, 1600
];

Output is as follows.

Qty 250 & 460 should be shown in the column "Qty for Min Releasenum"

[rubenmarin]

Hi, why those values should be in Min?

It's shown in max because in the first example when an SalesOrder & Deliverydate has only one releasenum it is shown only on max, so I added the If() to not show them on Min value.

[Vasu2021]

Qty 250 belongs to release number 4. For the sales order 55001, the min release number is 4 and the max release number is 7. In this case, Qty 250 should be shown under "Qty for Min Releasenum".

The same rule applied to Qty 460. For the sales order 55002, the min release number is 8 and the max release number is 9.so, this value should be shown under "Qty for Min Releasenum".

[rubenmarin · Accepted solution]

Ok, so ignoring deliverydate to get max or min, just by  SalesOrder, in that case you can use:

Aggr(If(ReleaseNum=Min(TOTAL <SalesOrder> ReleaseNum),Quantity), SalesOrder,Deliverydate,ReleaseNum)

Aggr(If(ReleaseNum=Max(TOTAL <SalesOrder> ReleaseNum),Quantity), SalesOrder,Deliverydate,ReleaseNum)

But if some sales order has more than two ReleaseNum, the middle ones will not be used. just the min and max realease num for each SalesOrder.