# Adding some but not all duration totals together

• 0
• Question
• Updated 3 years ago

I need to keep track of total benchmark of a participant’s jobs that have a gap of less than 30 days between them. For example (see below), I need to add just the # of days of the 1st & 2nd job together and NOT include the 3rd job's # of days because there is a gap of 51 days between 2nd & 3rd job.

Master table is Participants and Child table is Employments

Name  (ref. proxy)           Employer       Start            End            Duration

John Smith                      McDonalds    1/6/16        2/10/16      35 days

Then there is a 7 day gap between jobs

John Smith                      Walmart        2/17/16        5/3/16       44 days

Then there is a 51 day gap between jobs

John Smith                      Starbucks      7/2/16          8/3/16       32 days

Ideally, I want something that says that this person reached the 60 day benchmark because they have been “employed” (including a <30 day grace period) for 79 days. The next benchmark goal is 90 days.

Technically, the 3rd job has a separate benchmark of 30 days but I only need to display the highest level benchmark this person achieved which was 60 days. Any ideas?

• 0 Points

Posted 3 years ago

• 0
• 72,448 Points
Hi Jennifer,

I saw this question a while ago, but was off visiting glaciers, waterfalls and puffins.

To solve this one, you will need to float up the child data you need up to the Parent record.  There are a couple of ways to do this, and I will describe one of them.

1. Create a Summary field called Recent Start Date 1 which is the Maximum Start date.

2. Look that up down to the child records.

3. Create a summary field of the Maximum End Date called Recent End Date 1, subject to the filter that the Start Date = Recent Start Date 1.  ie it's the End Date for that First Start Date.

4. Copy the field  [Recent Start Date 1] and called it [Recent Start Date 2] and add the filter such that the Start Date is before [Recent Start Date 1]. So this will be the 2nd most recent Start Date.  Look that up down to the child record.

5. Repeat step 3 above, subject the filter that the Start Date = [Recent Start Date 2].

Keep repeating until you have as many child record data elements floated up to the Parent as are you need to account for.  It sounds like you are trying to calculate when certain benchmarks are hit, so there is probably a practical limit to how many children you would ever be dealing with, which are relevant.  ie, maybe up to say the last 6 jobs.

Then you would probably have a big IF statement to either add the days worked or add zero if the gap was too long.

You might simply the IF by making additional variables for the duration of each job. ie [Recent Job 1 Days] would be the formula numeric field

ToDays([Recent End Date 1] - [Recent Start Date 1]) + 1 ( you will want to count the both the start and end day as a day worked.)

Then your IF would be like

IF(

[Recent Start Date 7] - [Recent End Date 6] < Days(30), [Recent Job 6 Days],0)

+

[Recent Start Date 6] - [Recent End Date 5] < Days(30), [Recent Job 5 Days],0)

+

[Recent Start Date 5] - [Recent End Date 4] < Days(30), [Recent Job 4 Days],0)

+

[Recent Start Date 4] - [Recent End Date 3] < Days(30), [Recent Job 3 Days],0)

+

[Recent Start Date 3] - [Recent End Date 2] < Days(30), [Recent Job 2 Days],0)

+

[Recent Start Date 2] - [Recent End Date 1] < Days(30), [Recent Job 1 Days],0)

• 0 Points
Wow your trip sounds much more awesome than the one I just returned from. I read through your explanation and I think I can manage this so I will try it soon
• 72,448 Points
OK, let me know how it goes. (I was in Iceland)
• 0 Points
Thank you for the very detailed directions! I really appreciate it. It seems to be working. I did have to make a couple adjustments because I had said "end date" when I meant "most recent verification date" and I only wanted to deal with NEW jobs and not WORK HISTORY records. The dates are lining up correctly and the report looks good. However, there might be an issue with detecting gaps because sometimes users did NOT enter the jobs in chronological order. Moving forward we shouldn't have this problem because they should be entering in real time and not adding back data anymore. Thanks again.
• 72,448 Points
Great, thx for letting me know.