We are using one of the Business Content shipment extractors that is delivering us the shipment costs. Via the Logistics Extraction Cockpit we could also add the delivery quantity. However, the delivery quantity is stored on cost item level which means it is repeated on each line when a shipment cost document has multiple cost items. Let me give you an example :
This is how the extractor delivers the data :
Shipm nr Delivery nr Cost item nr Delivery quantity
==> As you can see we have one shipment that is linked to one delivery, but the delivery quantity is repeated for each cost item what is not really what we want since the total delivery quantity for this delivery is only 60.000 KG
When we run this in Bex, where we usually don't drill-down on the individual cost items this is what we get
Shipm nr Delivery nr Delivery quantity
==> We don't want to see this 120.000 KG but 60.000 KG. There is only a minority of shipments that have multiple cost item lines so we hope there is a solution in Bex.
This is what I tried already but nothing is 100% waterproof :
Making a restricted keyfigure that restricts on cost item number 1 or the delivery quantity will exclude those shipments that have only one cost item that starts with an item number higher than 1. These cases do exist, probably due to deletion of cost item 1 on R/3.
Creating an calculated keyfigure with exception aggregation "maximum" on cost item level works fine as long as the shipment is in the drilldown. When you aggregate on a higher level (plant, forw. agent, calmonth) the result can be incorrect as then the exception aggregation takes places on that level. It is incorrect for these cases where shipments don't have an cost item number 1. (since this is the biggest group he takes this when you use exception aggregation maximum, although some shipments only have a cost item 2 for example and these are then excluded)
Working with a counter "number of cost items" doesn't work as Bex doesn't permit us to do a division "delivery quantity/counter" on "before aggregation" level which is required to garantuee correct results.
So I'm completely stuck, however I feel there must be a solution in Bex. Can anyone advize ? My priority is to solve this in Bex but als on data staging level I don't immediately see a solution. The granularity of this standard extractor can't be changed and I don't immediately see a solution that is compatible in all scenarios (also when a cost item gets deleted).
Thanks for your feedback