1 Reply Latest reply: Dec 23, 2016 7:17 AM by Gysbert Wassenaar RSS

    Count records based on relative date

    Louise O'Rance

      Hi folks,

       

      I want to identify how many clients have 2, 3, x appointments within a given time period, where the start date is determined by the first appointment (so it's not a set period such as Jan-June).

       

      I have a table like so:

       

      Record_IDClient_IDAppointment_dateOutcome

      1

      ABCddmmyyyyA
      2ABCddmmyyyyB
      3ABCddmmyyyyC
      4DEFddmmyyyyB
      5GHIddmmyyyyC
      6GHIddmmyyyyB
      7JKLddmmyyyyA

       

      Record_ID has unique values for each row; the other fields may repeat values.

       

      First challenge

      For each Record_ID, I want to count the number of other records with the same Client_ID whose Appointment_date is after a defined period of the appointment date of the record in question. For example, for Record 1, how many other records does Client ABC have with an appointment date greater than [appointment_date(R1)] but less than [appointment_date(R1) + 183]. Call this Next6Months

       

      Second challenge

      Once I have calculated this for each Record ID, I want to identify the maximum value of Next6Months for each Client_ID. I think I can use aggr function for this - aggr(Max(Next6Months),Client_ID). I'll use that to create a dimension, grouping clients according to how many appointments they had in the space of 6 months.

       

      Lastly, I want to be able to study these patterns for given values of Outcome - how many clients had 5 appointments with the Outcome A in a 6 month period, for example. Outcome is a dimension used in other graphs in my app, so I just need the formulae used to calculate the above to be responsive to filters.

       

      Any assistance will be much appreciated! Thanks.

        • Re: Count records based on relative date
          Gysbert Wassenaar

          Try something like this:

           

          TempData:

          LOAD

               Record_ID, Client_ID, Appointment_date, Outcome

          FROM

               source_table

               ;

           

          OUTER JOIN (TempData)


          LOAD

               Record_ID as AsOf_Record_ID,

               Client_ID,

               Appointment_date as AsOf_Appointment_date

          RESIDENT

               TempData

               ;


          Data:

          LOAD

               *,

               If( Appointment_date <= AddMonths(AsOf_Appointment_date,6), 1, 0) as InNext6Months

          RESIDENT

               TempData

          WHERE

               Appointment_date >= AsOf_Appointment_date

               ;


          DROP TABLE TempData;


          You can then sum the field InNext6Months to find how many appointments fall in the six month period for each appointment. Dimensions: AsOf_Appointment_date, Client_ID. Expression: sum(InNext6Months)

           

          If you want the maximum you'll have to aggregate the sums per client and appointment to calculate the max:

          max(aggr(sum(InNext6Months),Client_ID,AsOf_Appointment_date))