Forum Discussion

KellyLyons1's avatar
KellyLyons1
Qrew Cadet
2 years ago

AR aging report/fields

I may be overthinking thhis, so I could use some help!

I am trying to create an AR aging report to calculate a running 0-30, 31-60, 61-90 and 90+.  I need to create these fields, and then break up the  ARtotals by customers for each AR date range above. 

I have a "Days in AR (Age)" field with this formula:

ToDays(Today()-[Date of Service])

And then I created a field "AR Age group" formula:

If([Days in AR (Age)] < 31, "0-30 days",
If([Days in AR (Age)] > 30 and [Days in AR (Age)] < 61, "31-60 days",
If([Days in AR (Age)] > 60 and [Days in AR (Age)] < 91, "61-90 days",
If([Days in AR (Age)] > 90,  "90+ days"))))

These both work great.  I guess I am struggling with what I need my end result to look like.  How do I extract all AR for Company A that is 0-30 days, 31-60 days etc?  And then have this displayed in a report?



------------------------------
Kelly Lyons
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    I think you are way past the difficult part already. All you need to do is make a summary report and decide if you want any road groupings like by Customer or by region, something like that, and then group columns by your calculating aging bucket, field



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • NathanMaubert's avatar
    NathanMaubert
    Qrew Trainee
    2 years ago

    Agree with Mark that you have done the leg work and a summary report should tick the box for you. Just did about the same thing to build an AR map by month. Group your rows by company and columns by your AR category. Build out a detail drilldown report to show the details you want.



    ------------------------------
    Nathan Maubert
    ------------------------------
  • BrianSeymour's avatar
    BrianSeymour
    Qrew Assistant Captain
    2 years ago

    I agree with Mark and Nathan for a higher level status of all your accounts. The summary report basically forms a matrix of all the combinations, such as Company and AR Age Group. You may want to try tinkering around with the "Cross Tab" options as well to essentially list the Companies in a left-hand column and the AR Age Groups across the top.

    Or you could add your AR Age Group field as a "Dynamic Filter" to table and other style reports too, if you are interested in seeing a list of records.

    Also, you may avoid "nested ifs" to make your formula a bit more readable, but it sounds like you've already got that bit dialed in!

    If (
    [Days in AR (Age)] >  90, "90+ Days"
    [Days in AR (Age)] >= 61, "61-90 Days",
    [Days in AR (Age)] >= 31, "31-60 Days",

    // Default
    "0-30 days"
)



    ------------------------------
    Brian Seymour
    ------------------------------