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!!!
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.
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. :)
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!