Hi Experts,
My custmer need is to get the balance by invoice from BW system.
In my DSO, I have the data stored the way below:
Company code------|--------Invoice NO-----|----------Posting data--------|--------Balance
X100-------------------|--------INV01------------|-----------01/10/2016---------|--------10.000
X100-------------------|--------INV01------------|-----------03/10/2016---------|--------5.000
X100-------------------|--------INV01------------|-----------12/10/2016---------|--------3.000
X100-------------------|--------INV02------------|-----------20/10/2016---------|--------20.000
X100-------------------|--------INV02------------|-----------22/10/2016---------|--------12.000
X200-------------------|--------INV03------------|-----------02/10/2016---------|--------15.000
X200-------------------|--------INV03------------|-----------05/10/2016---------|--------7.000
X200-------------------|--------INV03------------|-----------17/10/2016---------|--------500
X200-------------------|--------INV04------------|-----------03/10/2016---------|--------3.000
X200-------------------|--------INV04------------|-----------07/10/2016---------|--------1.000
The requierement is to get the balance by invoice and the sum of the balance by company.
In the example above, the current balance by invoice will be:
-INV01 3.000
-INV02 12.000
-INV03 500
-INV04 1000
And the current balanace by company will be:
X100 15.000
X200 1500
I tried to use "Last" Exception aggregation with reference characteristic "Posting date", but that works only when we analyse by invoice...And it displays wrong amount when we analyse by company code
X100 12.000
X200 500
Is there any way to get the sum of the min of balance/Invoice by company code without having to include the invoice number in the report
Thank's a lot for your help.
Abdess,
Hi,
You may use the "LAST" EXCEPTION AGGREGATION with reference characteristic to POSTING DATE, INVOICE NUMBER, and COMPANY CODE..You may have multiple reference characteristics if you have BW 7.4..If you are using lower version (BW 7.3 and below) you may create several three (3) FORMULAS:
FORMULA1 = BALANCE ("LAST" EXCEPTION AGGREGATION with reference characteristic to POSTING DATE)
FORMULA2 = FORMULA1 ("LAST" EXCEPTION AGGREGATION with reference characteristic to INVOICE NUMBER)
FORMULA3 = FORMULA2 ("LAST" EXCEPTION AGGREGATION with reference characteristic to COMPANY CODE)
Just post here if you have questions..
Regards,
Loed