Problem using set analysed

[bert_geraerts]

Dear all,  

I have the following problem using set analyses, and I was wondering if you guys could help me.

We have a table that contains all purchase lines in our company, for example:

IN_ProductNr
IN_DeliveryDate
IN_OrderNr
IN_QTY

In a charttable we would like to show the next delivery (based on DeliveryDate) and the QTY this order contains.
Showing the next delivery date isn't any problem, but I don't seem to be able to show the correct QTY.

When I use the following code, I only get a correct value when only one product is selected. 
Otherwise the result is always 0.

sum({$<IN_DeliveryDate={"$(=Min(IN_DeliveryDate))"}>}IN_QTY)  

When I use the following code then the QTY is the total for all outstanding orders.

sum({$<IN_DeliveryDate={"=Min(IN_DeliveryDate)"}>}IN_QTY) 

Could somebody please tell me is this is possible and how to do this?

[bert_geraerts]

I have the following problem using set analyses, and I was wondering if you guys could help me.

We have a table that contains all purchase lines in our company, for example:

IN_ProductNr
IN_DeliveryDate
IN_OrderNr
IN_QTY

In a charttable we would like to show the next delivery (based on DeliveryDate) and the QTY this order contains.
Showing the next delivery date isn't any problem, but I don't seem to be able to show the correct QTY.

When I use the following code, I only get a correct value when only one product is selected. 
Otherwise the result is always 0.

sum({$<IN_DeliveryDate={"$(=Min(IN_DeliveryDate))"}>}IN_QTY)  

When I use the following code then the QTY is the total for all outstanding orders.

sum({$<IN_DeliveryDate={"=Min(IN_DeliveryDate)"}>}IN_QTY) 

Could somebody please tell me is this is possible and how to do this?

[Gabriel]

Hi,

Is there are way to attached sample data?

In the absence of sample data, try "=$()"as below

sum({$<IN_DeliveryDate={"=$(=Min(IN_DeliveryDate))"}>}IN_QTY)

If you can attach sample data it will help

[sunny_talwar]

May be you need this

FirstSortedValue(Aggr(Sum(IN_QTY),  IN_ProductNr, IN_DeliveryDate), -Aggr(IN_DeliveryDate, IN_ProductNr, IN_DeliveryDate))

[bert_geraerts]

Hi Stalwar1, 

That is working perfectly!

Could you also provide the sollution if I needed the second ranked value?
So the delivery QTY for the second shipment ranked by delivery date.

Thx

[sunny_talwar · Accepted solution]

Add a 2 at the end like this

FirstSortedValue(Aggr(Sum(IN_QTY),  IN_ProductNr, IN_DeliveryDate), -Aggr(IN_DeliveryDate, IN_ProductNr, IN_DeliveryDate), 2)