Calculate aging over time

Where can I find the information I need to calculate customer aging over time? Usually when I get stuck I can copy something one of my predecessors did, but no one knows of any report like this previously. 

I want to be able to show a sales person, for instance, that their receivables for an account are increasing over time. Eg., when viewing the report in June, they'd be able to see in January that their net30 for a customer was $1000, February it was $1500, March.... etc etc. But, I'm stuck as to what would be my starting point to establish initial balance etc. I would assume it's something like transaction history, where to find quantity at a point in time I would calculate the flowing river after Jan 1 of each year... But again, stuck as to where to start... Maybe I need to establish that beginning yearly balance in a table and calculate from there to avoid the queries being too monstrous. Hope I'm not over thinking this. 

Parents Reply Children
  • 0 in reply to wishingforsql

    So you want something like an AR Aged Invoice report but instead of aging the Open Invoices by the aging buckets (30,60,90,120+) you want to show it by Current Year/Period.

    Prior  Jan  Feb  Mar  Apr May Jun Jul Aug Sep Nov Dec Total

  • 0 in reply to Doc102208

    Basically yeah. I want to be get the data together so I can show a chart/make obvious that a customer may be an AR problem with an increasing (positive trendline) of cash in the 60day/90day buckets. So when you open the application (external to sage) you'll be able to see that, oh no, every month the total amount owed at 60 and 90 is increasing. The starting point would be able to see the 60/90 in data format, obviously. 

  • 0 in reply to wishingforsql

    I've built Aging reports for AR & AP aged by Invoice Date, Invoice Due Date & GL Posting Date.

    Here is a screenshot of the AR Aging by Invoice Due Date.

    The report is built with a Pivot Table so if your only interested in 60, 90, 120 the Current & 30 Days can be removed.

    The report uses a Cutoff date entered at runtime.

    You can filter by Col headings.