Query calculation with data calculated in ERP

[Former Member]

Hi Gurus,  I have the following question:

one particular key figure is calculated and aggregated in ERP and available in ERP data source.

For example two data sets:

month, credit control area, company code, customer, payments, average delay on payments

...

April 2012, 1000, 0120, 12, 50000Euro, 20.

May 2012, 1000, 0230, 12, 100000Euro, 28.

...

The payments (aggregated all single payments to monthly payment), the average delay on payments is calculated.

My question:

when those key figures are calculated in ERP and transferred to BW like in this example, I can't calculate for every characteristic the average delay on payments.

That means for example, the average delay on payments of 20 is only correct in the combination

April 2012, 1000, 0120, 12, 50000Euro.

I can't do an calculation for example only for characteristic customer?

Is my understanding correct?

[former_member184494 · Accepted solution]

You need to set up exception aggregation on the same. But then if you want to aggregate the same based on customer - how would you do it - will it be an average or weighted average ..?

You will first have to do the aggregation determination for the delay on payments and then use exception aggregation so that the BW query calculates the same at a customer level.    

[Former Member · Accepted solution]

Arun Varadarajan wrote:
 You will first have to do the aggregation determination for the delay on payments and then use exception aggregation so that the BW query calculates the same at a customer level.     

That is what I am thinking too.

But I think the current case I can't do the determination for the delay on payments, because in the ERP table the information doesn't exist. Only average delay on payments exists. I think it would be necessary to have the raw data (delay on payments) in ERP table for each payment item/position. The ERP table is an own developed table (no BW standard table).

Then the calculation could be done in BW query easier for each characteristic level.

[former_member184494 · Accepted solution]

ERP is giving you the calculated value at a much lower level of detail.

month, credit control area, company code, customer, payments, average delay on payments

April 2012, 1000, 0120, 12, 50000Euro, 20.

May 2012, 1000, 0230, 12, 100000Euro, 28.

In this case you have the delay on payments at this level.

If you had to find the delay on payments for credit control Area for all of 2012 - what will it be ? (Assuming that these are the only 2 records you get )

will it be 48 (20 + 28 ) or

              24 ( simple Average ) or

          a weighted average based on the payment ( the weight calculation - I would leave it to you )

According to the logic you need - you need to create formulae in the query and have exception aggregation on the same so that the result calculates properly when you drill up or down.    

[Former Member · Accepted solution]

Arun Varadarajan wrote:
ERP is giving you the calculated value at a much lower level of detail.

You are correct and I can't disagree.

The main problem in my case is, that the ERP is giving me the calculated value (average delay). But I want do the calculation in BW (for average and weighted average).

For this I will need the higher level of detail for detail - the raw data for delay on payments - in ERP.

With this value I can calculate in BW with exception aggregation.

Is my final understandig correct?

[former_member184494 · Accepted solution]

I definitely does not hurt to have the original data coming into BW , but I would still disagree - lets assume that you get the delay in payments at a invoice level - even if you were to get the payment due date and payment date - there is nothing much you can do about it because the data is already at the lowest level and if you were to calculate the delay in payments - it would slow down your loading but still give you the same results as the original extractor.