Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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 # | PO Issue Date | Days Between (not in data) |
Test Vendor | 1234 | 1-1-2020 | blank |
Test Vendor | 1235 | 1-1-2020 | 0 |
Test Vendor | 1236 | 1-15-2020 | 14 |
Test Vendor | 1237 | 1-15-2020 | 0 |
Test Vendor | 1238 | 1-20-2020 | 5 |
**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.
avg(aggr([PO Issue Date] - above([PO Issue Date]),[PO Vendor Name],[PO #]))
I tried adding DISTINCT to the formula above but it still did not work.
Any help would be appreciated.
Thanks!
What if you calculate it by a different approach? Like this.
=(max([PO issue date]) - min([PO issue date])) / (count(distinct [PO issue date]) - 1)
What if you calculate it by a different approach? Like this.
=(max([PO issue date]) - min([PO issue date])) / (count(distinct [PO issue date]) - 1)
@Vegar - sometimes I make something very simple, very complicated. That worked perfectly. Thank you!