I am trying to calculate the average number of days between transaction dates for a specific vendor, where there are multiple transactions on the same date.
I need to do this on the front end and not in the load script so that if the user wants to analyze a certain time period, they can filter to specific years, months, weeks, etc. and the calculation changes.
Example table below:
PO Vendor Name
PO Issue Date
Days Between (not in data)
**Days between is just a column I added to show what I am trying to do, it is not actually a field in the data.
Here is the calculation I tried but it does not work, because if the Days Between is 0, then it skews the average. I need the script to look at DISTINCT days between. So it should be the average of (14 and 5) / 2 = 9.5 days. Not (0+14+0+5) / 4 = 4.75.