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_ID

Client_ID

Appointment_date

Outcome

1

ABC

ddmmyyyy

A

2

ABC

ddmmyyyy

B

3

ABC

ddmmyyyy

C

4

DEF

ddmmyyyy

B

5

GHI

ddmmyyyy

C

6

GHI

ddmmyyyy

B

7

JKL

ddmmyyyy

A

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.