1 Reply Latest reply on Dec 9, 2016 12:26 PM by Ojoswi Basu

    Calculated field help--finding active duration activities in a time series

    Sarah McGraw



      I have a dataset in which each record contains a participant ID, an activity start date, and an activity end date (and an assortment of other fields that don't pertain to this question).


      I need to put this data into a time series chart that shows whether a participant has an active activity at any given time, and I'd like to allow the user to drill down from a default quarter view. This means that the participant should be counted in every quarter/month/week for which that participant has a start date during that time period or in the past, and an end date in that same time period, in the future, or NULL. I honestly have no idea how to proceed here--it would be easy to create a calculated field to see whether the participant was active during any pre-defined time period, but I can't quite wrap my head around how to write a field to see whether the participant is active during ANY given time period for a time series. The final chart should be a column chart.


      dummy data:

      Participant numberActivity Start DateActivity End date


      Here, a quarterly view should count 2015 Q1 as having 2 participants (01 and 02), Q2 as having 3 participants (01, 02, and 03), Q3 as having 2 participants (02 and 04), and Q4 as having 1 participant (04, because there's no end date).

      A monthly view should show Jan as having 1 participant, Feb and Mar as having 2, April as having 3, May as having 2, June as having 1, July as having 2, and all subsequent months as having 1.


      How is this kind of problem usually approached--it must be somewhat common, right (burn down charts, etc)? I've seen a few similar questions in the forums, but either the problem was different enough that the solution did not seem to apply, or else the solution didn't make sense to me.


      Thank you!