Jump to content
We've recently updated our Privacy Statement, available here. ×

Adding running/cumulative count to a data table


Stephane Forrer
Go to solution Solved by Gaia Paolini,

Recommended Posts

Hi,

I'm looking to create a running count of when a Patient takes DrugA and inserting a new column as a transformation in the data canvas. I suspect I need to use intersect but can't seem to get it working. What I currently have is Count([Treatment]) OVER ([Patient], [Treatment]) which I isn't correct.

RunningCount is what I'm trying to calculate:

Patient | Treatment | RunningCount1111 | DrugA | 11111 | DrugA | 21111 | DrugB | 1112 | DrugB | 1112 | DrugA | 11112 | DrugB |1113 | DrugA | 11114 | DrugA | 11114 | DrugA | 21114 | DrugA | 3

Thanks!

Stephane

Link to comment
Share on other sites

Brilliant, thanks Gaia it works a treat.

My actual data has gaps below so I've used Calculate & Replace column to fill down, unless you know of a way to do it a single expression? :)

// Calculate & Replace column: "RunningCount" CASE WHEN LastValidBefore(Trim([Treatment]))="DrugA" THEN LastValidBefore([RunningCount])END

Updated Example Data:

Patient|Treatment|Timepoint|RunningCount1111|DrugA|0|11111||1|11111|DrugA|0|21111||1|21111|DrugB|0|1111||1|1112|DrugB|1|1112||1|1112|DrugA|0|11112||2|11112|DrugB|0|1112||1|1113|DrugA|0|11113||1|11114|DrugA|0|11114||1|11114||2|11114|DrugA|0|21114||1|21114||2|21114|DrugA|0|31114||1|3

Thanks

Link to comment
Share on other sites

  • Solution

Would this work:

  1. case
     
    when Trim([Treatment])='DrugA' then DenseRank(RowId(),[Patient])
     
    when ([Treatment] is null) and (Trim(LastValidBefore([Treatment]))="DrugA") then DenseRank(RowId(),[Patient])
     
    end

     

I only used Trim(..) as in my case I had some spurious spaces in the drug name, you probably don't need it if the field does not have leading or trailing spaces.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...