How to show cumulative customer line item amount
In this document , i will explain an example in real time related with cumulative customer line item. To get it , i saw that bex cells and complicated formulas was used but i aim that there will be simple way to get similiar result.
In ERP , FBL5N tcode provides this cumulative amount in last row. To calculate this , you have to need all financial records start of customer records until referance date.
In 0FIAR_C03 cube consist of all records of customer line item. Unfortunitely , they are stored different columns that they are 0DEBIT_LC –> Debit amount in local currency and 0CREDIT_LC –> Credit amount in local currency.
In this cube , you have a key figure named Amount in Local Currency with +/- Signs –> 0DEB_CRE_LC . This will show only sum of debit and credit records in related time interval. But , i want to show that cumulative customer line item.
Problem is that you restrict time interval with years and months when you can not get all fi documents. So ,you eliminates datas out of interval. For this reason , you have to find a way to calculate total balance amount.
Example like belowed :
KCUM_DEB : Cumulative customer balance ( Amount in local currency +/- Signs )
This key figure has special features ;
Noncumulative key figure , Inflow ( debit ) , Outflow ( credit ) , Aggregation -> SUM , Exception aggr –> LAST VALUE.
Currency : Local
What is the logic ?
This key figure added cube and you can get debit – credit value in all FI documents.
In bex ; this key figure calculate last value of customer total balance = cumulative customer line item.
Select Values for Variable : Period/Fiscal year –> 012.2014 is used to find last 12 months that is variable restriction.
Finally , you can calculate of cumulative costumer line item. However , you have not select all records of customer.
I hope that this document can be beneficial.
Also , you can find similiar logic in MM/Inventory Cubes to calculate stock quantities and values.