How to calculate a cumulative unique count

This is a little tricky to explain. It's not just a running SUM from month to month. What we are trying to do is count the number of patients seen in a month, and show how that accumulates over time. The last tricky part is that we are hoping to have it restart at the beginning of each fiscal year (based on a date dimension, not on the fiscal year in Sisense).

Here's some more detail. Let's just look at two patients over three months:

Patient 1 - Seen in month one. Not seen again
Patient 2 - Seen in month two. Seen again in Month three
Patient 3 - Seen in month three.

The count we are looking for here is:

Month 1 == 1 //because only one person was seen
Month 2 == 2 //because we are still including Patient 1, even though they weren't seen again, plus patient 2
Month 3 == 3 //because patient 1 was seen in month one, we continue to include them, patient 2 was seen in month 2 and 3, but it's unique count so we are only counting them once, and we have patient 3 seen for the first time

I tried using PREV and got to the point where the first and second months were what I wanted, but it breaks in Month 3 when the PREV month no longer has an entry for the patient.

We have full control over the data warehouse, so ultimately I could just use SQL to make a table for this, but I was really hoping there would be a Sisense answer for this so it could be managed in a dashboard rather than in SQL Code.

Anyone solved something like this before!?

Thanks,
Chris

cjakerle

Posted 2 years ago·Last reply 2 years ago

3 comments

DRay

·2 years ago

Hi cjakerle.

Thank you for reaching out.

I have a couple of questions that will help us determine the best way forward.

1. Have you defined a custom fiscal year in your data model? If not, you’ll need to create a fiscal year and fiscal month definition in your data model. If you have full control over the data warehouse, adding these as fields in your date dimension table would be beneficial.

2. Do you have a cumulative count of the patients? If not, you may need to do a little bit of SLQ to create a custom table. It might look something like this.

sql
SELECT 
    t1.FiscalYear,
    t1.Month,
    COUNT(DISTinct t1.PatientID) OVER (PARTITION BY t1.FiscalYear ORDER BY t1.Month) as CumulativePatients
FROM 
    YourTable t1

This SQL snippet uses a window function to count distinct patients cumulatively over each month, partitioned by the fiscal year. Make sure to replace YourTable and column names accordingly.

3. Once you have your SQL logic ready and tested:

ElastiCube: Add this as a custom table or a pre-build SQL in your ElastiCube using the Elasticube Manager.
Dashboard: Create a new widget in your Sisense dashboard to display this data.

I hope that is a start at least. Feel free to reach out if you encounter any issues or need further assistance with setting this up!