Emmanuel Chukwunyere 2 Posted April 2, 2020 Share Posted April 2, 2020 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. Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted May 13, 2020 Share Posted May 13, 2020 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] Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now