Adding running/cumulative count to a data table

[Stephane Forrer]

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

[Gaia Paolini]

This worked for me with your example:

case when Trim([Treatment])='DrugA' then DenseRank(RowId(),[Patient]) end

 

[Stephane Forrer]

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

[Gaia Paolini]

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.

[Stephane Forrer]

Yes this worked thank you! 👍