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
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
