HarrisonSmith
Qrew Trainee
17 days ago

# Sum Overlapping Time/Remove Timeframe Gaps

Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records.

Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.

• DonLarson
Qrew Commander

Do you need a total for all time or will you have to specify specific periods?

For a small number of records in the People table I think I have a solution.  If there are thousands of People than a solution is in order.

• a total for all time. I have a calc start & end formula date fields that put a cap on the timeframe if the provided date ranges exceed the relevant timeframe. There are currently 100 claimants, 210 residence records, and 40 employment records, knowing that will increase in the future, but it shouldn't get into the 1000's.

• Would it be OK to bucket the analysis into say monthly buckets? Say you made 36 monthly buckets for the last 36 months? There may be a solution if we use that approach. It's brute force with 36 or 72 summary fields, but that might work.

• Well my ultimate goal is I need to categorize each person into 6 buckets:

• lived/worked within 2 mile radius for >= 10 years
• lived/worked within 2 mile radius for >= 5 years
• lived/worked within 3 mile radius for >= 10 years
• lived/worked within 3 mile radius for >= 5 years
• lived/worked within 4 mile radius for >= 10 years
• lived/worked within 4 mile radius for >= 5 years

The proximity distance field is a numeric field that exists on each residence/employment record (anyone who is categorized into the 2 mile/10 year bucket will automatically fall into each subsequent bucket, etc.).

My assumption was that whatever solution can work for 1 bucket can be duplicated for the others, so I would ultimately probably need 3 numeric fields (1 for each distance threshold) that specifies the amount of months each person spent at that distance, to go along with 6 formula checkboxes (1 for each category above) that look at whether each distance duration number is >= 120 (for 10 years) or >= 60 (for 5 years).

• If you'd like you can contact me directly by email and I will have a quick look at your app. A possible outcome is I have a look and estimate the time required to work with you to get this working and then you'd have to decide if you have a small consulting budget to work with.

mark.shnier@gmail.com

• DonLarson
Qrew Commander

The most elegant solution is Tableau or PowerBI.   This has alot of potential out comes and if your reporting requirements expand, it might be very hard to get new data.

For a pure Quickbase solution, I would create three fields on each person.

2 mile count

3 mile count

4 mile count

At the end of the month I would have a Pipeline go through your child tables and then update the count on the Person table.    You will need to do a Search inside of a Search to check both child tables and then if either or both are true, only add one more month to the appropriate field for the totals.