BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
christine_kim0
Community Trekker

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

10909_pastedImage_0.png

Thanks!

Christine

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

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
ms
Super User ms
Super User

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.

christine_kim0
Community Trekker

Re: JSL: Cumulative calculation with different baselines

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

0 Kudos