Average difference between two dates based on two other columns

[Flawn]

Hello Everyone, 

I asked this question a couple weeks back but didn't get a satisfactory answer, so I will post it one more time in hopes of getting one. 

We have three columns relevent to our problem: 1. A Client ID column. 2. An appointment classification column, which for our purposes has two relevent buckets; 'service requests' and 'other'. 3. An appointment creation date column.

We need to calculate the average difference in days between a service request appointment and the next  'other' appointment a client attends.

I am finding it very difficult to construct a formula that can keep track of the clients as well as identifying which is the 'next' appointment after the service request without any other identifiers (which, unfortunately, I am unable to add due to the way their database has been constructed). Perhaps the solution is simpler than my brain is making it; so i'll pass it on to other, more capable minds! The dataset is very large and confidential so i've constructed a simplified example of the data in question below and 'color coded' the pairings that should be matched and have their differences calculated for the average to better illustrate the objective.

Client ID	Appointment Category	Date (dd/mm/yyyy)
1	Service Request	01/06/2020
1	aaa	09/06/2020
1	bbb	07/06/2020
1	ccc	05/06/2020
2	ddd	02/06/2020
2	Service Request	06/06/2020
2	eee	11/06/2020
2	fff	15/06/2020
3	Service Request	4/06/2020
3	ggg	9/06/2020
3	Service Request	14/06/2020
3	hhh	19/06/2020

Of course, I am happy to elaborate if i was unclear on anything or if I missed some edge case that could effect the answer!

Thank you all so much in advance for your time!

Regards,
-Flawn

[Flawn]

I asked this question a couple weeks back but didn't get a satisfactory answer, so I will post it one more time in hopes of getting one. 

We have three columns relevent to our problem: 1. A Client ID column. 2. An appointment classification column, which for our purposes has two relevent buckets; 'service requests' and 'other'. 3. An appointment creation date column.

We need to calculate the average difference in days between a service request appointment and the next  'other' appointment a client attends.

I am finding it very difficult to construct a formula that can keep track of the clients as well as identifying which is the 'next' appointment after the service request without any other identifiers (which, unfortunately, I am unable to add due to the way their database has been constructed). Perhaps the solution is simpler than my brain is making it; so i'll pass it on to other, more capable minds! The dataset is very large and confidential so i've constructed a simplified example of the data in question below and 'color coded' the pairings that should be matched and have their differences calculated for the average to better illustrate the objective.

Client ID	Appointment Category	Date (dd/mm/yyyy)
1	Service Request	01/06/2020
1	aaa	09/06/2020
1	bbb	07/06/2020
1	ccc	05/06/2020
2	ddd	02/06/2020
2	Service Request	06/06/2020
2	eee	11/06/2020
2	fff	15/06/2020
3	Service Request	4/06/2020
3	ggg	9/06/2020
3	Service Request	14/06/2020
3	hhh	19/06/2020

Of course, I am happy to elaborate if i was unclear on anything or if I missed some edge case that could effect the answer!

Thank you all so much in advance for your time!

Regards,
-Flawn

[amitchandak · Accepted solution]

@Flawn , Create a new column like this and take the average

new column =
var _max = maxx(filter(table, [Client ID] =earlier([Client ID]) && [Date] <earlier([Date])),[date])
return
datediff( maxx(filter(table, [Client ID] =earlier([Client ID]) && [Date]= _date && [Appointment Category] = "Service Request"),[date]),[date], day)

[Flawn]

Hello Amitchandak,

Sorry for the delayed response. The results are close to what we want, but not quite there: the calculated column produced a lot of negative values. The objective is to only calculate the average difference in days between service requests and the next non-service-request appointments for that client - so negative values shouldn't be occuring (as the next appointment to occur can't be before the date of the service request). 

We need to make some sort of adjustment to ensure that we are only calculating the difference in days for appointments that are occuring after Service Requests, not before.