Aggregating Data from same value from different row in Spotfire

[Emmanuel Chukwunyere 2]

I've some clinical data that needs some tricky calculations.

I have a column called "LabCode" and another "Days" which is my aggregate data/Measure.The third column is my expected output after the aggregations.

The labcode are the high (H) or Low (L) dosage from the test results from an individual on the trial.

The doctors want to know how many days the person was on low dosage before high dosage and vice versa.

I want OVER Function expression that can show the results in yellow highlights.

Thanks for your help.

[Khushboo Rabadia]

I am not sure how is your complete data but if dataset has only these two columns then this can be achieved. If there are more columns, that will need to be adjusted in over statement. This can be achieved through some calculated columns

 

2. Data > Add calculated column... ### row id for each row

Column name: ID

Expression: RowId()

3. Data > Add calculated column... ## get labcode for previous row on the current row

Column name: PrevCode

Expression: First([Labcode]) OVER (previous([iD]))

4. Data > Add calculated column... ## get labcode for next row on the current row

Column name: NextCode

Expression: First([Labcode]) OVER (next([iD]))

5. Data > Add calculated column... ## get days for next row on the current row

Column name: NextDays

Expression: First([Days]) OVER (next([iD]))

6. Data > Add calculated column... ## get only next row days where current and next lab code is different. Then display the same over all previous rows with no data till there is a valid value

Column name: B

Expression: FirstValidAfter(case when [Labcode][NextCode] then [NextDays] end)

7. Data > Add calculated column... ## get only previous row days where current and previous lab code is different. Then display the same over all next rows with no data till there is a valid value

Column name: A

Expression: LastValidBefore(case when [PrevCode] is null then [Days] when [Labcode][PrevCode] then [Days] end)

8. Data > Add calculated column... ## Expected result

Column name: ExpectedResult

Expression: - [A]