We’ve created a query that calculates average days for customers to pay us. However, does anyone have an idea of how to revise this formula to exclude any result (payment date - invoice date) < 5?
We’d like to exclude results < 5 because we consider these anomalies.
Slight complication, wondering if anyone has a quick fix:
sum((case when convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) > 5 then convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate)) else null end))/count(Customer.CustID)
Above formula works, but “/count(Customer.CustlD)” is still counting cases where days to pay is < 5. So, for example, if one customer had 2 invoices, and paid 1 of those in 2 days, and the other in 8 days, the formula is resulting in an average of 4, (8/2 invoices) instead of 8.