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. 

  • 0 in reply to wishingforsql

    This sounds like a neat report idea.  I'd love to collaborate with you on it.

    Another way to look at this:  measure average days to pay or average daily balance.

    You could also create a new metric - DDO - or DollarDaysOutstanding and graph that.

  • 0 in reply to wishingforsql

    IM history is all in one table, so calculating point in time values is not all that hard (just add everything from the beginning of time up to your date). 

    AR data is not so simple, being located in multiple tables, and much more complicated with different transaction types looking very different.  Look at the actual data in those tables and you'll quickly see what I mean (invoices, payments, credits, adjustments, applied discounts, finance charges...).  To calculate aging buckets, you can't just add everything up from the beginning of time, you need to see what each invoice balance is at that point in time, with aging dates based on the original entry.  Throw in "fun" possibilities like how payments can technically be dated before the original invoice... and it becomes a real headache to do well.

  • 0 in reply to Kevin M

    True that! Which is a big part of why I got stuck. You're helping me see through the trees, though. That said AR_InvoiceHistoryPayment is blank, but I should be able to correlate OpenInvoice to CashReceipts. I cte's or temp tables in my future... 

  • 0 in reply to wishingforsql

    That is the wrong table (stores details of posted CC payments).

  • 0 in reply to Kevin M

    CC = credit card? It appears to store all types of data, from check to cash to CC. The CheckNo column either has a check number or some sort of prefix, VISA, CASH, etc. 

  • 0 in reply to Kevin M

    All good points.  I have been building these reports for years now and understand the complexities.

    It seems like the user wants to see a trend of a customer's payment performance.  We should be achieve thet, even if it glosses over a few nuances.  items that skew that trend can be addressed (may be with some difficulty and by accepting a margin of error)

  • 0 in reply to wishingforsql

    Ignore that table entirely.  It's not what you want.  Cash Receipts history is stored in AR_CashReceiptsHistory.

    Edit: wait.... you might need that data for CC payments processed during invoice data entry.

    Seriously, going to the raw transaction history tables is making your life much more complicated.  Sage 100 is a complex system, with bits of data scattered in multiple places.  There is a very good reason why Sage programmed the AR_OpenInvoice and AR_TransactionPaymentHistory tables to manage invoice balances. 

  • 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.

  • 0 in reply to Kevin M

    AR_TransactionPaymentHistory was not part of my replication schema. Doh! Just had a look at the actual production table and now I see. A lot of relevant info. I'll add it to the replication model. I was initially looking at AR_InvoicePaymentHistory, which was blank. 

    AR_CashReceiptHistory seems to include all payment types. I'm still not sure what is wrong is wrong with this table other than it is volumunous, like trying to use AR_InvoiceHeaderHistory (or detail, etc). 

  • 0 in reply to wishingforsql

    The problem is understanding every data source fully, every field, every nuance... odd transaction types, weird dates... table data you didn't think to look at until months later when a user asks about a number not matching what they see inside the system.  Then joining it all together, to perfectly reproduce invoice balances at any given date... when even the Sage Aging reports aren't perfect at this.

    I've been burned by underestimating the complexity of data too many times (including with AR / AP Aging specifically), when simpler approaches would have achieved the business goals with much less effort / cost. 

    If you want to go dive into the complexity, good luck! Slight smile

    The possibilities are not limited like simple IM +/- calculations.