# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor

## Calculate Average Days Between Distinct Dates

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!

1 Solution

Accepted Solutions
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
2 Replies
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Contributor
Author

@Vegar  - sometimes I make something very simple, very complicated. That worked perfectly. Thank you!