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 ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendWould 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).
- MarkShnier__You
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