By using this website, you agree to our Terms of Use (click here)
Hi Tim!
Hope you have been well! I was hoping you could help me with designing an inquiry that shows the due date of an invoice for our customers, and date that shows when the invoice was paid in full. My boss is wanting to see the average amount of days it takes a customer to pay an invoice in full. I have some ideas on how to do this, I would just need the due date of each invoice, each invoice reference, the amount paid column, Amount Paid Date, and the customer information. I venture over to the Invoice Screen (Where the invoices are entered in Acumatica) I see the table name for that screen is AR Invoice. I can go to the applications tab, and see the columns I need, and I see that table name is ARAdjust, the field for the Amount Paid column is Display Curry Amount, but when I enter it in my results grid, the whole field is blank. 🙁 If you have an easier way to build an Inquiry for this, I would love to hear that as well. Thanks Again Tim!
Hi Potato_Ghost,
I'm still loving your avatar by the way.
I haven't thought much about the "partial payments" part of your situation, but I think that's going to be tricky.
To get something started, let's just assume full payments for now.
I'm not sure why you're getting an empty value for the Curry Amount, but my first guess would be that you added two tables on the Tables tab, but didn't put in anything on the Relations tab. Is that possible?
I threw something together with this on the Tables tab:
This on the Relations tab:
Note: I'm pretty sure that the "adjd" in the ARAdjust fields stands for "adjusted" and the "adjg" in the ARAdjust fields stands for "adjusting" which is why I'm using the "adjd" fields to join to the ARInvoice table (the "adjusted" document) and the "adjg" fields to join to the ARPayment table (the "adjusting" document).
Then this on the Conditions tab:
And, finally, this on the Results Grid tab with a field for showing the amount of days between when the invoice was due and when it was paid.
Let me know if this gets you headed in the right direction. Then we just need to solve the problem of the partial payments.
Firstly, I have to say that may be the most awesome avatar/name-pairing I have ever seen!
Doug Johnson just posted a workaround in the ideas here: https://feedback.acumatica.com/ideas/ACU-I-1300
Hi Tim!
I am sorry it has taken me this long to respond, it has been a very busy week at the office, and I have finally just received some breathing room. I am very happy you are still enjoying my Potato Ghost Avatar.. lol Thank you very very much for getting me going, I had been joining the tables, but I was trying to pull the amount paid and the payment date out of ARAdjust Table, and was not having much luck. Pulling it out of the payment table makes much more since. I too had thought of the problem of partial payments, so I thought if I could use the grouping tab to group by Invoice, it should sum the days between due date and payment date. For Example, if it takes a customer 25 days to partially pay the invoice 001 and then finally pays the rest 5 days later, it should still give me the last payment date if I group by the Invoice reference number, and still get a count of 30 days. Here is what my grouping tab looks like.
This seems to work perfectly, but I also needed to add an extra condition since the inquiry was counting open invoices that had been paid on partially, which would not give me a true total of the average days it takes each customer to pay their invoice in full. For this reason, I went ahead and added this to the Conditions Tab.
This gave me the exact information I needed to build an awesome addition to a Power BI Report I had. I opened up my pbix file I used to publish my old report, then added my new inquiry that you helped me with, and since I had grouped by invoice number, then I could make a join of the two data sets by Invoice Number. At first, I though it probably was not necessary to do the join, since I could not pull customer out of the old data set, and average days out of my new data set to get average days paid by customer, but it did turn out useful for adding the chart payment collections Timeline. I was able to pull revenue out of my old sales data set and payment date out of the new data set, and add a neat little timeline for collections. I feel like it may be off a tad since the customer may not have paid the full balance of the invoice on the last payment date if they had made a partial payment before, but this is rare in our company, so the chart could still be an indicator. Thank you so much for all the help. Here is a pic below of the final result, please do not mind the scribbling lol, I wanted everyone to see where I was going with this. Let me know what you think!
Very interesting. Thanks so much for sharing this.
A couple of questions:
- If the Invoice Due Date is 5/1/2017, for example, and the First Payment Date is 5/15/2017, that's 14 Days to Pay. Then the Second Payment Date is 5/20/2017, that's 19 Days to Pay. If you group and Sum, wouldn't that give you 33 (14+19) Days to Pay? Don't you need to group and Max to give you 19 in this scenario?
- Where did you get the idea for the Total Days Between Payment KPI? It's an interesting idea and I'm still trying to figure out if it's a meaningful number to me.
- Is your Payments Collection Timeline just looking at the total amount of payments received each month or is it looking at the average days to pay each month? Either way it looks great. It's amazing what a little visualization can do.
Hi Tim!
I did finally get some time to test the inquiry and had to make a small adjustment. Please see the responses to your questions below.
- If the Invoice Due Date is 5/1/2017, for example, and the First Payment Date is 5/15/2017, that's 14 Days to Pay. Then the Second Payment Date is 5/20/2017, that's 19 Days to Pay. If you group and Sum, wouldn't that give you 33 (14+19) Days to Pay? Don't you need to group and Max to give you 19 in this scenario?
Actually the formula you gave me counted the days just fine once I grouped by the invoice reference number. Since every invoice number is unique, and we lumped all the information in by invoice number, this returned one row per invoice number, therefor one payment date per row. The payment date that it returned happened to be the most recent, so the date diff formula just counted the days between the due date, and that one payment date. So there was no double counting. But! I did find out I needed to add an additional condition to make ARPayment.Status Equal Closed as well. The reason for this, is that the amount paid was doubling when a payment was voided because when it groups by invoice, it also sums the payments on the invoice, and as you know, when you void a payment on an invoice, both the voided payment and new payment still stay in the application on that invoice, but there is a negative amount recorded as a void payment to balance that out. Since we had a condition that ARAdjust.ADJGDocType equaled PMT, it was not picking up the voided payments to lower the amount paid to what it should be. But that is fixed now, and everything works great!
Where did you get the idea for the Total Days Between Payment KPI? It's an interesting idea and I'm still trying to figure out if it's a meaningful number to
It is probably not super useful information lol, but I wanted to show that the averages were accurate, so I made that column for total number of days between payment, and I just recently fixed my number of payments made column, by doing a count measure in Power BI on the payment numbers, so I could show that the average days it takes our customers to pay an invoice in full were accurate. Below, If you divide the number of payments for each customer by the total number of days to pay a full invoice, you get the average number of days.
Is your Payments Collection Timeline just looking at the total amount of payments received each month or is it looking at the average days to pay each month? Either way it looks great. It's amazing what a little visualization can do.
Thanks Tim! I love the visualization, it really helps us digest the information much quicker. It is the amount of payments received in the month. So it is telling us our cash position regarding collections each month. That is fixed now as well after I added that condition I told you about above regarding not double counting payment amounts on invoices that have a voided payment on them. It is amazing to look at our bank statements, and see the total credit to our account that month total what is on that report graph. However, It is not always an exact match of our bank statement, since payments by check to our company are recorded in Acumatica when they are received, and if it is toward the end of the month, we may not cash the check until the next month, but it is a great measuring stick to show trends for cash flow each month, and when we need to prepare for some slow collections from our customers.
Very cool. I like it a lot. Thanks again for sharing what you created.
Thanks for this. Very helpful for a new client of ours
Just a +1 on this as this seems to be a common customer request. Tim, thanks for the insight (slash speculation) on the adjg/adjd nomenclature. I noticed that the default relationship that Acumatica's Generic Inquiry provides uses only joins on ARAdjust.adjd. Once I modified the joins I was better able to see the applied documents. Thanks Tim!
Online Members
