Aggregation problem, solvable in Bex ?

[Former Member]

Hi,

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

300.......80936387.....1................60.000 KG

300.......80936387.....2................60.000 KG

==> 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

300.........80936387............120.000 KG

==> 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

[Former Member]

Hi Wim,

may be this could be a solution for your problem. It is not completely BEx-based since you'll need to change the InfoCube.

1. Create a new Characteristic as a combination of delivery nr and item nr without masterdata

2. Add this characteristic to the infoCube

3. Create UdateRule for this characteristic concatenating delivery nr and item nr.("80936387"+"0001"="809363980001").

4. Create a calculated key figure with exception aggregation "Max" or "whatever-you-need" on this characteristic. On higher aggregation levels the outcome will for sure produce the maximum or "whatever-you-need" overall shipment/item combinations without adding up the items per shipment. if the shipment is in the drilldown. The output should the quantity of the item with the maximum quantity. if the item is in the drilldown it should exactly produce the item's quantity.

We have used this in a different scenario and it works on all aggregation levels. The numbers will only double if navigation attributs on the "item" level are used in the drilldown that differ from item to item but this doesn_t seem to be a requirement, here.

BR

Volker

[Former Member]

Hi Volker,

First of all sorry for my late reaction but I was on holidays for a while.

Thanks for your suggestion. However I'm affraid this does not solve my problem. When I create such a "concatenated" characteristics the problem remains on a higher level. (also see the suggestion with the compounded object above)

Let me give an example :

Imagine that I run the report with only plant as a drill-down characteristic. Let's say that this plant has 3 shipments assigned to it. Then I want to see the total delivery quantity of all these shipments of that plant without adding the individual items per shipment. When I put exception aggregation on this concatenated object "delivery-item" or "shipment-item" it will only show a value related to ONE shipment. So this solution is fine as long as shipment is in the drilldown and I need a solution on a higher level as well. I really think this is not possible in Bex, also due to a very poor "before aggregation" functionality.

Or did I misunderstand something ?

Thanks for feedback,

Wim

[Former Member]

Hey Wim,

A workaround for ur problem might be, Having the Delivery no. in the report and use a Condition of Top N using '1'. You might want to use 'No Display' for the delivery no.

I myself have been trying to do something similar, but need to display the min/max/avg. all on the same report and havent been able to find a way of doing it. Do post if you could figure a way

Doniv

Message was edited by: doniv ramuk

[Former Member]

Hi Aneesh,

Thanks again for your suggestion. Including the delivery quantity AVG exception aggregation in another calculated keyfigure and afterwards putting it on "before aggregation" is also something that I tried before. This leads you to very annoying and confusing Bex Behaviour. You have the possibility to set this keyfigure on "before aggregation" but once you check/execute the query it says that "before aggregation" is not permitted. I'm seriously dissapointed with this "before aggegation" functionality anyway generally speaken because I can hardly see any circumstances in which it really gives an added value since it almost never works.

I have e.g. the possibility to make a counter "number of cost items" and put this one one before aggregation. But then there isn't any way at all to divide the delivery quantity by this counter on before aggregation level. Even not if I use the NODIM function which gives me the possibility to put the delivery quantity on "before aggregation" as well. So even when both basic keyfigures that are used in the division are on before aggregation it doesn't work...

So after all, I think I have to conclude that this is simply unsolvable in Bex while it looks pretty easy at first sight with all these exception aggregation and before aggregation functions.

One of my biggest problems is that I don't really see a good solution on data staging level neither. I could e.g. try to fill a characteristic with an 'X' on the first cost item line for each shipment to be able to build a restricted keyfigure in Bex later on. But then I first should load the shipment-cost item link in an ODS and read this during the load of shipments. So far no problem at all but what when a certain shipment cost item will be deleted ? I think I will get seriously stuck then since my 1st item will probably still have this 'X' while the quantity got cancelled. To create a logic that is compatible with all situations including deletions should not be underestimated in my opinion.

My preferrence is to solve this in Bex but if anyone knows a 100% waterproof solution during data staging I'll be happy to hear that as well.

Hi Doniv,

Thanks for your suggestion too. In my case I would not necessarility have to build an condition on delivery number since the result is already fine once the shipment number is included so I could put the shipment number also on hidden but this is definitely not what we want since this is causing dirty behaviour. If you ask the total on plant level you will first see 1000 lines with individual delivery quantities (hidden shipments) and also from a performance point of view this is not good ofcourse.

Thanks

Message was edited by: Wim

[Former Member]

Hi Wim,

try a restricted key figure with a constant selection on Cost Item wihtout filter values. This avoids the aggregation over Cost Item, but the key figure will be aggregated over all other characteristics.

[Former Member]

Hi Maik,

Thank you for your answer. I forgot to mention in my original post that I have already been playing around with constant selection without an success. I tried it again one more time based on your suggestion and it does not solve my problem, the quantities are doubled again in this example but to be correct I have to say that apart from cost item there is also another characteristic on this same level of detail (cost item category) so probably that explains I guess ?

My feeling is that only "before aggregation" can solve this correctly but this is only possible in very limited circumstances. I hope someone knows a trick or has another suggestion ?

Thanks again

[Former Member]

Wim,

Use exception aggregation. Create a calculated key figure with the formula as equal to the quantity key figure. Press OK. In the properties, press enhance and select Average or Max or Min and in the reference characteristic select Cost Item InfoObject. This Key figure will now give you the quantity averaged out across the various cost items.

if cost item is added to the drill down, then the quantity will be repeated.

This will work only if reporting is at a document level. If document is removed from the drilldown, then you won't get correct results. To get around that problem, cost item can be compounded with shipment number.

Cheers

Aneesh

[Former Member]

Hi Aneesh,

Thanks a lot for your answer. The first part of your suggestion is exactly what I tried (max aggregation on cost item) and this works indeed very fine but I couldn't find a way to use it on a higher aggregation level. I will try your suggestion about compounding and let you know if this works. Thanks !

[Former Member]

Wim,

If you are going to work on a higher aggregation level, then use average.

Cheers

Aneesh

[Former Member]

Hi Aneesh,

I created a new object (ZCOSTITEM) that I made compounding with shipment number.

I have based the exception aggregation (I tried AVG and MAX) on this compounded infoobject. As long as the shipment number is in the drill-down, everything is fine.

When I remove the shipment number and run the query on a higher level (plant, creation date,...) the result is still not correct. When I use "MAX" aggregation it is now picking delivery quantity from the shipment with the highest quantity while before (when I didn't use the compounding) it took the maximum value on shipment cost item level which is much closer to reality ofcourse. (as most quantities or on item 1 which is always the biggest, except from some rare cases)

Did I do something wrong or do you have another suggestion ? Also using the AVG exception aggregation gave an incorrect result.

Thanks in advance !!!

Message was edited by: Wim

[Former Member]

Wim,

Last idea:

Create another calculated key figure using the exception aggregation key figure. In the properties, select before aggregation.

Set the exception aggregation to average.

If before does not work - give after a try too.

If it still doesn't work, then next idea goes towards data model change

Cheers

Aneesh