A/R Aging or Overdue Analysis in Calendar Months

[Former Member]

Hi,

Considering the standard SAP Delivered 0FIAR_C03_Q0005 Query which is the Overdue analysis query based on Accounts Receivable cube, I have a custom requirement where the aging has to be done in calendar months compared to days as delivered in the business content.

I have been trying to do that but this where am stuck, the way SAP is calculating Aging buckets is by hard coding the columns (1 - 30 days, 31 - 60 days and so on) and my requirement requires to be Dynamic. Here is how the report should look like assuming we are in September -

Comp Code Acc Doc # Invoice # Sep'2010 Aug'2010 Jul'2010 Jun'2010..................

For my requirement I don't want to change the way SAP has proposed to calculate because that's the true way of calculating Aging but am not sure how I can achieve the months one.

Please provide your suggestions.

[Former Member]

Hi

As Manyak said, SAP standard report calculates over due based on Net Due date, Posting date ,clearing date and user entered key due date. So please find out your requirements on what basis you need to calculate monthly.

Thanks

Raghu

[Former Member]

Thanks Mayank & Raghu,

The requirement here is to build an Aging report based on Due Date &Document Date. The way SAP calculates its Aging is in days and which is the true Aging and as Mayank has explained based on the Key Date entered and the Offsets & Value range comparisons in the Key figure definition the days buckets are defined.

The requirement was to reflect the same aging derivation in calendar months instead of days. Now am aware that with the standard Aging provided by SAP this is not possible, so was checking if anybody else had this similar requirement.

Mayank, I would like to understand more about the logic you have specified in the end "make different key figures based on month end date. and use the end date for each month to calculate whether an amount is overdue or not untill that date of the month."

Can you please be more specific on this (Elaborate the logic for month end date for each key figure), I would like try this out. Let's just try for 1 month, anyways my requirement is to have 2 years of buckets.

Thanks a Lot.

[Former Member]

Hi Guru,

I don't know any way to achieve your requirement in BW... the only thing that comes in my mind is to make different sets of key figures for each month. But If we do that, the performance of the report will be very very bad. Anyways, here is how we can do this:

Customer exit formula variable (ready for input) will hold the key date entered by user. Then make 11 more cust exit variables that will hold month end dates for other 11 months.

Suppose user enters 30.03.2010. Key figure Total Overdue amount as on 30.03.2010 will be (due date > key date1 *amount)

Key figure Total Overdue amount as on 28.02.2010 will be (due date > key date2 *amount)

Key figure Total Overdue amount as on 31.01.2010 will be (due date > key date3 *amount)... and so on

Similarly, we can also calculate overdue 0-30 days, 30-60 days etc.. for each month..

Along with 12 key dates, 12 text cust exit variables also need to be created to display the description of KFs.

This is a working solution. But not a recommended one.

Best Wishes,

Mayank

[Former Member]

Thanks Mayank, Let me try this.

[Former Member]

Hey Mayank,

I don't think the logic you suggested will yield true aging, because where are you considering Posting Date, amd what do you mean by *Due Date > Key Date 1 * Amount*.

[Former Member]

Hi,

Due date i s Net due date , based on the posting date of that document and based on credit period it will be calculted at ECC it self

key date is nothing but which date we are enetering in varaible screen.

. it may be based on your postin date or may be clearing date.

, But for your requiemnt it is just idea only , but i do not know how it fits with your requirement.

really it is based on purely posting date

posting can be assigned to calmomth.

in calmonth . there is a cstomer exit varaiable Cmonth (current month )

amount can be resticted with that -

---

current month

offse-1 -- previus month

offset-2 -

---

pre previoues month

like that

that same varaible you can use in text varaible also , it gives the month also

if the users do not want to enter the data in varaible screen, by default it wil come.

but the users wants to eneter write the customer exit code by i_step= 2 , take the use entry varibale value based on that calculation to be done.

but mainely my conceren is the , the over due not based on psoting date it must be mainely with net due date .

Thansk & regards,

sathish

[former_member205352]

Assuming you have fixed number of Aging buckets you can use text variables of type customer exit(hence making it dynamic) to populate the description of these aging buckets.

Hope this helps.

[Former Member]

Hi Praveen,

Thanks for your reply, but it's not the question of getting the columns description but its about getting the monthly buckets (i.e. populating the correct values on monthly basis rather than in days).

The standard query basically calculates the Overdue amounts by hard coding the offset values in the columns and hence derives in days.

Please refer the std Query "0FIAR_C03_Q0005" to get an idea.

Thanks.

[Former Member]

Hi,

I don't understand your requirement clearly.

Whether an amount is overdue or not is decided based on dates. Due date and the open items at key date. Difference of these 2 dates gives us the no. of days by which the amount is overdue.In the standard SAP report if the difference is 25 days , the amount will appear under 0-30days bucket.

What I mean to say is, the report shows the situation as on the key date entered by the user. Monthly report as such is not possible because in that case you don't define a date for which you can calculate the overdue period. However, if you can make different key figures based on month end date. and use the end date for each month to calculate whether an amount is overdue or not untill that date of the month.

Best Wishes,

Mayank