BookmarkSubscribe
Choose Language Hide Translation Bar

JSL: Cumulative calculation with different baselines

Hi,

Does anyone know of a way to write a script to perform separate cumulative calculations across rows based on different baselines?  I'm trying to create a projected visit date report based off the max visit date for each subject.

The projected visit date column below shows the desired result based on the 'Max(VISITDT_RAW)' column + the cumulative sum from the 'Target Days from Previous Visit' column.  The 'Cumulative Calculation" column shows the desired calculation for the Projected Visit Date column.

Any help I can get is much appreciated Thanks!

Christine

1 ACCEPTED SOLUTION

Accepted Solutions

Re: JSL: Cumulative calculation with different baselines

If you look for a formula column solution, the below approach should work. Just paste the code below into the formula edit field of the Projected Visit Date column. You may need to check that the column names match yours exactly.

:Name("Max(VISITDT_RAW)") + In Days(

Eval(Eval Expr(Col Sum(:Target Days from Previous Visit, Row() <= Expr(Row()), :Subject)))

)

If you add the formula by script it must be enclosed in Name Expr( ).

For very large tables a scripting solution would be more efficient.

2 REPLIES 2

Re: JSL: Cumulative calculation with different baselines

If you look for a formula column solution, the below approach should work. Just paste the code below into the formula edit field of the Projected Visit Date column. You may need to check that the column names match yours exactly.

:Name("Max(VISITDT_RAW)") + In Days(

Eval(Eval Expr(Col Sum(:Target Days from Previous Visit, Row() <= Expr(Row()), :Subject)))

)

If you add the formula by script it must be enclosed in Name Expr( ).

For very large tables a scripting solution would be more efficient.

Re: JSL: Cumulative calculation with different baselines

Yay, this worked!  This is great.  Thanks so much for your help!!