distinctCount help

[Joe1]

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

[Max]

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