How to calculate a cumulative unique count

[cjakerle]

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

[DRay]

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!

[cjakerle · Accepted solution]

David,

Thanks so much for the thoughts.  We do have a fiscal year in the data model.  When I attempt to use a version of this query (modified for our data model) in the cube as a custom table, the OVER and PARTITION keywords don't appear to be recognized by Sisense (L2023.11) ... they don't turn purple like the words SELECT, FROM, JOIN, ON, ORDER BY, etc do and it gives a syntax error after the "OVER".  I can add this to the database instead of the cube and then pull in a table so it's not essential that this work in the cube, but I wanted to double check if you thought those window function keywords should work in L2023.11 for a custom table?

I'm not sure the SQL is quite working for this case, but I think you've confirmed that there's no way to do this in a dashboard and I should try to SQL my way through this.  That is actually a sufficient answer so I'll be all set once I know about those window functions being recognized (or not) in the custom tables.

Thanks!
Chris

[DRay]

Thank you for following up. Sorry the SQL didn't work, I'm having issues with my testing environment so I wasn't able to test it. I'm glad you have a way forward though. Please let us know if you need any more help.