nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table scripting help: Syntax for new formula columns, with conditions for row and column values

TRR21

Have a table with several hundred rows with the following structure:

ID	Visit	Date	Value
1001	BL	ddMONyyyy1	y1
1001	wk6	ddMONyyyy2	y2
1001	wk24	ddMONyyyy3	y3
1002	BL	ddMONyyyy4	y4
1002	wk16	ddMONyyyy5	y5
1003	BL	ddMONyyyy6	y6
1003	wk 6	ddMONyyyy7	y7
1003	wk 24	ddMONyyyy8	y8

Note that the number of visits, what they are called can vary for subjects. But they all have a BL.

Needs:

1. For each given subject "ID", I need a column, say "Value delta" that computes difference between their "Value" corresponding to "BL" visit to any other visit. Expecting a 0 for rows with BL and postitive or negative value for other visit rows for each subject.

2. Like above, I need a columan, say "Time delta" that populates with the difference in no. of days between BL and other visit dates for each subject. BL rows will be expected to say "0" days and others with postitive or negative integers.

3. Need a third column that calculates a "Slope" based on all the "Value delta" and "Time delta" for each subject. Some subjects may not be computable if there is only one visit.

Any help will be greatly appreciated!

Thanks.

Accepted answers

Thierry_S

The trick I use is to create columns for the Baseline data and dates on a table sorted by IDs AND Visit
Example for the Value (New column name = "Baseline Value")

If( :Visit == "BL",
:Value,
Lag( :BASELINE Value, 1 )
)

You then only need to a create column with the difference between Value and Baseline columns
Note: for the date, there is a special function to calculate difference:

Date Difference( :DATESTART, :DATEEND, "day" )

Hope that works for you.
If not, please attach a table with real or mock data where I could add the appropriate columns for you.
Best regards,
TS

All comments

Mark_Bailey

This request has been asked before. Will this approach work for your situation?

TRR21

My situation seems different than that in those examples.

@TRR21 perhaps you can accomplish what you are looking for by using the lag function. As well as previous post linked by @Mark_Bailey . You can search the lag function on the discussion board for relevant examples.

Thierry_S

The trick I use is to create columns for the Baseline data and dates on a table sorted by IDs AND Visit
Example for the Value (New column name = "Baseline Value")

If( :Visit == "BL",
:Value,
Lag( :BASELINE Value, 1 )
)

You then only need to a create column with the difference between Value and Baseline columns
Note: for the date, there is a special function to calculate difference:

Date Difference( :DATESTART, :DATEEND, "day" )

Hope that works for you.
If not, please attach a table with real or mock data where I could add the appropriate columns for you.
Best regards,
TS