How to partition running_sum calculation in Tableau?

Question

I currently am trying to accomplish the following SQL within Tableau's LOD + table expressions:

SELECT PATIENTID,CLINIC_ID,DATE,QUANTITY,SUM(QUANTITY) OVER (PARTITION BY PATIENTID ORDER BY DATE ASC) AS RUNNING_TOTAL
FROM "MY_SCHEMA".MY_SUMMARY
ORDER BY PATIENTID,DATE ASC

Normally, I would just use the above in a Custom SQL Datasource but I need it in LOD + Table calculations because I want the starting date to be customizable from a param I have on the dashboard.

I have tried the following:

RUNNING_SUM({fixed [Patientid],[Clinic Id]: SUM([Quantity]) })

But this gets the following error:

All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources

I can successfully do:

RUNNING_SUM(SUM([Quantity]))

But of course that is just the running sum overall, and not per patient/clinic.

I've also tried:

{fixed [Patientid],[Clinic Id]: RUNNING_SUM(SUM([Quantity])) }

But that gets the error:

Level of detail expressions cannot contain table calculations or the ATTR function.

Answer 1

You can do the running sum calc, as you already have it, but you'll also need to create a composite key between Patient ID and Clinic ID, which would go on the Detail shelf, so you're able to instruct Tableau to restart the running sum calculation using the field.