Micah Member

Hi Shane, Not quite. I ended up having Support help me with a solution. This version has been working. Using DateCreated as your start date and Invoice Date as your end date, you should be able to apply the same logic... AVG(CASE WHEN `DateCreated` = `InvoiceDate` THEN 0 ELSE DATEDIFF( CASE -- move end date to friday if on…

It works! Thank you very much!!!

Hi Richard, Thanks again for your help on this. It seems to be working, except when we cross over to a new year. For example, if a "Start Date" is in December 2015, and the "End Date" occurs in January 2016, the numbers are greatly inflated. Any ideas? Micah

Or... Does this formula make more sense to get to the average... sum(DATEDIFF(`InvoiceDate`,`PromiseDate`)) / COUNT(DATEDIFF(`InvoiceDate`,`PromiseDate`)) And can we apply the "less weekends" formula to this?

Hi Richard, Thanks again for your quick reply. Yes, you are correct, the lead time should be the number of business days from creation to invoice... I applied a simple calculation at first, showing the average lead time including weekends, and the numbers were significantly lower. Currently, with the weekends removed…

Yup, verified. My only changes were your Start Date is my DateCreated, and End Date is my InvoiceDate. It may be in my application of "Average" that's throwing the numbers off? I'm just aggregating the Average once I add the field to my graph. Should we be averaging the results within the beast mode?

Hi Richard, Thanks for your quick reply. Yes, Date Created is my start date, but Invoice Date is actually my end date. The transactions are unrelated to today's date, as I'm looking to track "Lead Time" for orders to be completed. Hopefully that helps clarify. Would that make a difference in the calculation? Micah

Hi Richard, Coincidentally, I had the same question... How many "business days" between start date and end date... But it doesn't seem to be working as my # of days goes up instead of down. Additionally, I'm trying to get to the average # of business days between start date & end date in a time period (like quarter) and am…