Forum Discussion

cjakerle's avatar
cjakerle
Cloud Apps
07-05-2024
Solved

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 tim...
dashboards & reporting
  • cjakerle's avatar
    cjakerle
    07-11-2024

    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's avatar
DRay
Admin
07-08-2024

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!