Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Ryan_Lessig
Contributor
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 NamePO #PO Issue DateDays Between (not in data)
Test Vendor12341-1-2020blank
Test Vendor12351-1-20200
Test Vendor12361-15-202014
Test Vendor12371-15-20200
Test Vendor12381-20-20205

 

**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
Vegar
Partner
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

View solution in original post

2 Replies
Vegar
Partner
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

View solution in original post

Ryan_Lessig
Contributor
Contributor
Author

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