Retention Days Between Contracts

[jessimica602]

Hey all!!

I am building a report for marketing/retention and have an issue I can't quite think of a way to get around.
We have contract employees and I am trying to find time between contracts. _days_between won't quite work since each set of contract data sits in its own row.


ID   Name        Contract ID    Contract Start   Contract End
12345   John Doe   65465      1/1/2021   3/6/2021
12345   John Doe   65893      4/10/2021   8/22/2021
12345   John Doe   78526      12/8/2021   3/22/2022

From the example about - I would need the time between contract 65465 contract end date and contract 65893 contract start date.

I thought of using ranking but some people will have just one contract others will have 10+. For some reason I just can wrap my head around a good way of showing the from one contract end to the next contract start.

Any help is greatly appriciated.

Thank you!!!

[bus_pass_man]

This expression returns 445 for your data, which I think is what you are looking for.

_days_between (
Maximum( Contract_End for Name)
,
minimum ( Contract_Start for Name)
)


You would need to use a coalesce with the maximum if contract_end could be null.

[jessimica602]

Close but not quite - we are looking for time between each individual contract so it should look like:

ID                 Name   Contract ID   Contract Start   Contract End   days between contracts
12345   John Doe      65465               1/1/2021    3/6/2021           First Contract
12345   John Doe      65893               4/10/2021    8/22/2021              35
12345   John Doe      78526               12/8/2021    3/22/2022              108

I feel like I'm over thinking this a bit. I was thinking about using rank and then two fields one for "if rank 1 then contract end else null" and another "if rank 2 then contract start else blank" and in another query that references this first one and use _days_between for those two new fields.....
I think that is really going a round about way to get what I need.

[jessimica602]

I got it!! Incase anyone is curious or needs this for future stuff. I built two queries with exactly the same fields. There is a Rank field and a Rank+1 so

ID       Name   Contract ID   Contract Start   Contract End   Rank  Rank+1
12345  John Doe   65465              1/1/2021      3/6/2021     1       2
12345  John Doe   65893              4/10/2021      8/22/2021     2       3
12345  John Doe   78526             12/8/2021    3/22/2022     3       4


I joined the two queries by the ID and the Rank field from query one and Rank+1 from query 2. Cardinality of one and only one for both relationships.

In the join query I only brought over the basic information - none of the rank fields, then created a new field called "Start for Days Between" with the following expression:

IF([Query1].[Rank + 1]=[Query2].[Rank])
Then([Query2].[Start Date])
Else ('')

and then the actual Days Between Field:

_days_between ([Start for Days Between],[Scheduled End])


End Result:

ID            Name   Contract ID   Contract Start   Contract End   Days between
12345   John Doe   65465                1/1/2021     3/6/2021               0
12345   John Doe   65893               4/10/2021     8/22/2021      35
12345   John Doe   78526              12/8/2021     3/22/2022       0



Yay!