How to calculate a cumulative unique count | Help and How-To

cjakerle · 2024-07-05T18:58:40.494000Z

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 againPatient 2 - Seen in month two. Seen again in Month threePatient 3 - Seen in month three.The count we are looking for here is:Month 1 == 1 //because only one person was seenMonth 2 == 2 //because we are still including Patient 1, even though they weren't seen again, plus patient 2Month 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 timeI 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

MoreDiscussions

How to calculate a cumulative unique count | Help and How-To | Sisense Community