https://netsuiteprofessionals.com logo
Join Slack
Powered by
So I'm trying to build a SS that measures Percenta...
# administration
d
So I'm trying to build a SS that measures Percentage of Invoices Paid on Time. I pull in the Due Date and the System Notes Date of when the bill was changed to Paid In Full. A couple issues: 1) I can't perform a "clean" calculation because one is date and the other is date/time (producing a result like in the screenshot even when the "dates" are the same (ideally, so long as the date itself matches, would like to produce this as a 0); 2) if a bill is paid/unapplied/paid again, I get duplicate results (in other words, I have more results here than just a list of all paid bills).
m
Have you tried the Date Closed field for invoices. I believe that is the same as the date paid
Then do a formula percentage field Case when {dateclosed} > {duedate} then 0 else 1 end Summary type = average
d
I've seen some conflicting info RE the Date Closed field: "Date Closed is sometimes mistaken as the date of payment application which changes the status of an invoice from Open to Paid in Full. Date Closed is the most recent date of payment made regardless of when it was applied"
Even that article is weird. Says there's no standard field on the record for it, but says to use it for the search anyway. I suppose they just mean that you can search for it but not find it on the record itself, which makes sense.
Ugh, it's kinda worse than I imagined: Date Closed is the transaction date of the bill payment in this scenario I was seeing stuff like this:
in that example, the bill payment was created on 1/4, but has a trandate of 1/6
m
are you tying to find date paid of Vendor Bills or Customer Invoices?
d
vendor bills
i'm just going to present that information to the person that requested this info and say "you tell me which date you want" and outline the downside of the system notes/non-integer issue and let them decide
thanks for chiming in, as usual
m
No problem. The other option is to create the saved search on the Type = Bill Payment. Then companies trandate of the bill payment to {appliedtotransaction.duedate}
You may get duplicate invoices but that’s what happens if they make multiple payments against an invoice. Using the average percentage formula column if two payments are made before due date then the average would be 100% If one before one after then average would be 50%. (And so on) so this would be accurate as week but would require a summary search
My last option would be to create a scheduled workflow or script that would look for Bills where status = paid in full Then would update a custom field on the Bill record called paid in full date. This would source from the MAX({trandate}) of the applying vendor payments. Then you could easily compare this field to the duedate per vendor bill (Note this would require override period restrictions and allow non gl changes to be enabled)
d
In that last instance, the MAX trandate of the applying vendor payment would be the same as the Date Closed field, no? My issue with that field/result is that a payment could be applied to a bill on the 1st but have a trandate of the 15th and that's what would show as the Date Closed on the bill, when it was really, actually paid in full on the 1st.
I added the Applying Transaction Date Created and Applying Transaction fields, but........the results are the exact same as the System Notes for when the bill was changed to PIF, which makes sense:
Forgot that I can use Function on the results to select "Day"....though I'm guessing formulas tied to it will remain wonky.
I don't understand this bit: Then do a formula percentage field Case when {dateclosed} > {duedate} then 0 else 1 end Summary type = average Never used Formula (percent) before. What is the desired outcome here?
Deciding I don't care about the integer/rounding issue. Got this far:
m
Oh I thought you were trying to find some sort of percent paid on time metric per vendor
If you group by vendor it will show you what percentage of invoice you’ve paid on time per vendor
d
On a macro level, yes. Trying to show "if we had 20000 bills in 2023, what % were paid on time?" Still not totally sure how I'm going to group like that, but I'm taking it incrementally. But also, the formula didn't work when I attempted Formula(percent).
m
Did you get an error with the formula? Also you could add a formula text to pull the year from the trandate and group by that TO_CHAR({trandate}, ‘YYYY’)
12 Views
Open in Slack
PreviousNext
Powered by