Subscribe Bookmark RSS Feed

JSL: Cumulative calculation with different baselines

christine_kim0

Community Trekker

Joined:

Jan 7, 2016

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

10909_pastedImage_0.png

Thanks!

Christine

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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
Solution

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.

christine_kim0

Community Trekker

Joined:

Jan 7, 2016

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