distinctCount help

Hi everyone,

I have a question about conversion but I can’t quite figure out how to do it. I would like to find out a distinct count of the ID (in this example, account_id and patient_id are the same), where there was at least one session (each row) in weeks_from_reg 0 AND 1 AND 2 AND 3. Essentially how many people had at least one session per week in the first month of their registration.

In the example above, I would expect the distinct count to be 2 as only patient_id 1 and 4 had at least one row in each (0,1,2,3) weeks_from_reg.

Anyone know how I can make a calculated field to do this?

Thanks in advance

Is this dataset the lowest granularity of your dataset?

If so, can you just count to see how many rows there are of the patient_id, partitioned by patient_id if the weeks_from_reg is less than 5?

ifelse(countOver(ifelse({weeks_from_reg}<5, {patient_id},NULL),[{patient_id}],PRE_AGG)=5,{patient_id},NULL)

This will return the patient_id which you can do a distinct_count on

1 Like