multiple set expression problem.

[#1]

Hello,

I am working on table for a client, and we are experiencing some difficulties with 1 column. "The Quantity on first deliviry".

We have several dimensions, with two expressions where the second give us trouble.

1ste expressions is "The first delivery date". This one works.

Min({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>}StockMov.MoveDate)

2nd expressions is "The quantity from the first delivery" This one fails.

Sum({$<StockMov.MoveDate={$(=Min({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>}StockMov.MoveDate))},StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>}StockMov.QtyMovedSt)

We want the sum of StockMov.QtyMovedSt where the date is equal to the first delivery date (first column)

This only works for the first row, and not for following rows...

Can anybody explain us why? And maybe correct us?

Thank you.

[swuehl]

That's probably because

$(=Min({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>}StockMov.MoveDate))

is returning the min MoveDate in global context, not filtered by your dimension values. You haven't given much information about your dimensions used, but I assume the min MoveDate must be evaluated in their context, right?

[#3]

Hello swuehl,

yes we where thinking that would be the problem but we could not figure out how to let the min movedate be evaluated in the context of the right dimensions.

Im trying to upload an example, but the file is too big. Im trying to reduce it. But it is correct as you say the min movedate has to be evaluated in the context of the dimensions chosen.

[christian77]

Create a variable like,

FirstDelivery = aggr( min(Date), Each Field you want to group by)

[#5]

Hi, Thx for your answer

I've created a variable firstDelivery with the following aggr

date(aggr(min(StockMov.MoveDate),ADRESGROEPFULL, CuPrRange.CustNum, CuPrRange.POSNum ))

This works, and gives me the same result as the first column. However when i use it in my expressions, it still does not work.

Sum({$<StockMov.MoveDate= {$(firstDelivery)} ,StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>}StockMov.QtyMovedSt)

[#6]

anybody has an idea? Still havent figured out how to do this. In essence I need the sum of the qty of the first delivery day. I thought is was going to be easy, but i can't managed to get it right.

[swuehl]

You should be able to get what you want by using FirstSortedValue(). If you need to aggregate your QtyMovedSt, you'll need to use advanced aggregation within FirstSortedValue, too.

Something along these lines:

=FirstSortedValue({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>} aggr(sum({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>} StockMov.QtyMovedSt),ADRESGROEPFULL, CuPrRange.CustNum, CuPrRange.POSNum,StockMov.MoveDate), aggr(StockMov.MoveDate,CuPrRange.CustNum, CuPrRange.POSNum,StockMov.MoveDate))

Could maybe be simplified or maybe needs adjustments, can't tell by the information you posted here (it's much easier to help if we know a little about your data model and chart dimensions).

Regards,

Stefan

[#8]

Hi Stefan,

I've managed to reduce the size, and i've attached it.

The problem is in the first chart, there are 2 expressions. The 'First Delivery date' and the 'Qty on first delivery date'. The first record is correct, but then we get all zero's.

I've tried firstSortedValue but then when there are 2 deliveries on the first day it would take only the first. Where we need the sum of both deliveries on that first day.

I hope you understand a bit, when you have more questions please ask.

I want to thank you already for time and help.

[swuehl · Accepted solution]

That's quite a large sample...

ADRESGROEPFULL is not a field in your current data model.

So try this simplified version of my above expression:

=FirstSortedValue(distinct {$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>} aggr( sum({$<StockMov.DwDocType={'SHP'},StockMov.OrdType-={'FCO'}>} StockMov.QtyMovedSt),CuPrRange.POSNum,StockMov.MoveDate), aggr(StockMov.MoveDate,CuPrRange.POSNum,StockMov.MoveDate)

If you don't need to be selection sensitive, you can also calculate the sum of  first deliveries in the script.

[#10]

that did the trick! Thanks a lot!