HEALTHCARE ANALYTICS
  • healthcare analytics
  • Resume Examples
  • healthcare analytics
  • Resume Examples

Healthcare Analytics​

resources from my career

Calculate Time in Therapeutic Range for a population using the Rosendaal Method in Excel

11/3/2015

 
In this post I will provide
  1. Links to and excerpts from references I used to create the TTR methodology;
  2. A step by step guide to creating a TTR calculator in Excel to systematically calculate TTR for each patient in an entire population;
  3. A link to the PDF copy of the internal documentation of this process which is more printer-friendly as well as a bit more concise.
Please click the Read More link below for the full post. And please leave a comment if you have found this information helpful.

​If you are interested in downloading the template, e-mail me here.

Click "Read More" below and over to the right...
​Reference #1: Percent of Days in Range (Rosendaal Method)

Article: http://www.inrpro.com/article.asp?id=1
Example: http://www.inrpro.com/rosendaal.asp

Excerpt:
“This is the most complex of the calculations, as it looks at the amount of time between visits to determine how long the patient might have been within their therapeutic range. If a patient has a therapeutic range of 2.0 - 3.0, and on May 1st tested at 2.5, and then tested 3.5 on May 31st, then we can determine how many days were in range. Since there were 30 days between tests, you assume that the patient slowly moved from 2.5 to 3.5 over those 30 days, so around May 15th, the patient was probably over 3.0, and therefore was out of range. Therefore, we estimate that 15 days were in range, and 15 days were out of range (within the 30 day time period), which means the patient is within range 50% of the time.”

Reference #2: Warfarin Time in Therapeutic Range - Supplemental SQL Logic Reference
http://www.healthit.gov/archive/archive_files/HIT%20Standards%20Committee/2013/Clinical%20Quality/2013-11-14/CMS179v2_UserGuide_SQL_Logic_Reference%20Jun%202013.pdf
Excerpt:
“Warfarin time in therapeutic range is the percent of time in which patients with atrial fibrillation or flutter who are on chronic Warfarin therapy have INR test results within the therapeutic range (2.0 - 3.0) during the measurement period.
 
“The following filters are applied to the INR results prior to the calculation of TTR for each patient:
1) INR value closest to 2.5 when there is more than one INR result on a single date
2) INR values greater than 10 will be replaced with an INR value of 10
3) INR values less than 0.8 are ignored and eliminated from the final TTR calculation for each patient
 
“The logic keeps track of the number of valid INR intervals for each patient. A Valid INR Interval is defined as a pair of INR start dates that are less than or equal to 56 days apart. Patients without 2 such intervals will be excluded from the calculation of the providers’ Average PctTTR later on.”

TTR Calculation Steps
There are 11 steps with Excel formulas and print screens. The print-screen image of each step is below the step text.
​Step 1: Pull patient INR results using a query similar to the one below. Your query should
  1. Pull all INR results for the population of interest within your desired timeframe
  2. Eliminate results < .8 and change any result > 10 to 10
  3. Rank results by date for each patient
  4. Pull only patients' results who have more than 1 result​
select * , rank () over (partition by MRN order by resultdttm asc, numericresult desc) as rank
into #PatientINR
from
(select distinct MRN
, patientid
, EntryName
, ResultDate
, case when NumericResult > 10 then 10 else numericresult end as NumericResult
from [Results Database] a
inner join [Patient Data] b on a.patientID = b.patientID
 where a.EntryName like '%INR%'
and numericresult > 0.8 ) A
 
select A.* from #PatientINR A

where MRN in (select MRN from #PatientINR where rank =2)
Other criteria for a successful TTR calculation will be addressed in the Excel steps.
​
Step 2: Paste results into Excel
Picture
Step 3: Create column for number of days between results
  1. Use DAYS360 function in Column H (“D”) to calculate number of days between result in previous row and result in current row.
  2. Add “IF” functions to formula in order to have “0” value for the first row for each new patient and also replace values over 56 with 0 since intervals over 56 days are not valid. 
  3. Final formula: =IF(G4=1,0,IF(DAYS360(D3,D4,FALSE)>56,0,DAYS360(D3,D4,FALSE)))
Picture
Step 4: Create Column “A”  for the “High INR value” and Column “B” for the “Low INR Value”
  1. If the current result is higher than the previous result (for the row), return the current result as the “High INR,” else return the previous result. Drag down formula.
  2. If the current result is higher than the previous result (for the row), return the previous result as the “Low INR,” else return the current result.
  3. Drag down formula.
Picture
Picture
Step 5: Create Column “(A-B)/D” for the total change between the INR results divided by the number of days between the INR results is the “Change per Day.”
  1. Note: If the value of D is 0, this will result in a “divide-by-zero error” and the rest of the calculations to arrive at the TTR will not be made. This is desirable since we do not want to calculate TTR for invalid intervals.
  2. Drag down formula.
Picture
Step 6: Create Columns for “Days out of Range on Low End” and “Days out of Range on High End” in columns L and M
  • ​Days out of Range on Low End = (2-B)/[(A-B)/D]​
  • Days out of Range on High End = (A-3)/[(A-B)/D
Picture
Picture
Step 7: Insert formulas for calculation
​
1.     For Days out of Range on Low End (in column L): =IFERROR(IF(AND(K4=0,E4<2),H4,(2-J4)/K4),0)
  1. This formula performs the operation (2-B)/[(A-B)/D]
  2. (2-B) = (2 minus the low INR value) which will account for any movement from below the therapeutic range into the therapeutic range
  3. Dividing the change between “under” the therapeutic range into the therapeutic range by the total change per day equals the total number of days “under” the therapeutic range
  4. Remember – the Rosendaal Method assumes consistent change per day throughout the INR Interval between results

Example:
  • (Please note the INR result is a ratio and thus has no units.)
  • A valid INR interval consists of an INR result of 3.0 on 11/26/2013 and an INR result of 1.7 on 12/27/2013. 
  • Thus, the high INR for the interval (A) = 3.0 and the low INR for the interval (B) = 1.7. 
  • The total change for the interval is (A-B) = (3.0-1.7) = 1.3
  • The number of days is from 11/26/2013 – 12/27/2013 which is 31 days. 
  • The Change per Day is (A-B)/D=1.3/(31 days)  ≈ 0.041935/day
  • The amount of change between “under” the therapeutic range into the therapeutic range is (2-B)=2-1.7=0.3 because everything between 2 and 3 is in the therapeutic range.
  • The number of days out of range on the low end = the change from under the range into the range divided by the change per day:
  • (2-1.7)/(0.041935/day) = 0.3/0.041935 days≈ 7.154 days
Picture
​
2.     For Days out of Range on High End (in column M): =IFERROR(IF(AND(K4=0,E4>3),H4,((I4-3)/K4)),0)
  1. This formula performs the operation (A-3)/[(A-B)/D]
  2. (A-3) = (the high INR value minus 3) which will account for any movement from above the therapeutic range into the therapeutic range
  3. Dividing the change between “above” the therapeutic range into the therapeutic range by the total change per day equals the total number of days “above” the therapeutic range
  4. Remember – the Rosendaal Method assumes change per day is consistent throughout the INR Interval
Example:​(Please note the INR result is a ratio and thus has no units.)
  • A valid INR interval consists of an INR result of 2.8 on 1/19/2012 and an INR result of 3.3 on 2/23/2012. 
  • Thus, the high INR for the interval (A) = 3.3 and the low INR for the interval (B) = 2.8.
  • The total change for the interval is (A-B) = (3.3-2.8) = 0.5
  • The total number of days is from 1/19/2012 – 2/23/2012 which is 34 days. 
  • The Change per Day is (A-B)/D=0.5/(34 days)≈ 0.014706/day
  • The amount of change between “above” the therapeutic range into the therapeutic range is (A-3)= 3.3-3=0.3 because everything between 2 and 3 is in the therapeutic range.
  • The number of days out of range on the high end = the change from above the range into the range divided by the change per day:
  • (3.3-3)/(0.014706/day) = 0.3/0.014706 days≈ 20.4 days
Picture
3.     Drag down formulas
     Notes:
  1. If the Low INR is > 2 then the value in column L will be negative. This simply indicates there was no movement below the therapeutic range.
  2. Likewise, if the High INR is < 3, the value in column M will be negative. This indicates there was no movement above the therapeutic range.
  3. Negative values will be treated as zero values in our final calculations
Step 8: Create column for Days in Therapeutic Range
  1. The complete formula is [D- (2-B)/[(A-B)/D]  – (A-3)/[(A-B)/D]] , which is just the total number of days in the interval minus the days out of range on each end.
  2. Nested “IF” functions were used in the formula for column N to make the calculation: =IF(G4=1,0,IF(IF(AND(L4<0,M4<0),H4,IF(L4<0,(H4-M4),IF(M4<0,(H4-L4),(H4-M4-L4))))<0,0,IF(AND(L4<0,M4<0),H4,IF(L4<0,(H4-M4),IF(M4<0,(H4-L4),(H4-M4-L4))))))
Picture
Note:
Most of the calculations so far have been done in the second record because the first record does not have a previous value.
The next calculations aggregate the TTR for each patient. They will be done in the first record and, using “IF” statements, they will be repeated when the value in column G (rank) is equal to 1 (once per patient).

Step 9: Create column for Patient Days TTR  (Number of days for each patient in therapeutic range)
  1. This formula will sum the days between intervals in the therapeutic range for a total number of days in the therapeutic range
  2. The formula says: if the “rank” = 1, sum all the columns where the patient MRN equals the MRN in this record. If the rank ≠ 1, then return nothing. =IF(G3=1,SUMIF(A:A,A3,N:N),"") [Where column A contains the MRN and cell A3 is the row where the rank = 1.]
Picture
Step 10: Create column for Patient TTR %  (Percentage of days for each patient in therapeutic range)
  1. This formula will divide the total number of days in therapeutic range by the total number of days between all valid intervals. This is not the same as the number of days between the first and last result, as a valid interval is defined as 56 days between results.
  2. TTR % = (Total Days in Therapeutic Range)/(Total Days in Valid Intervals) x 100%
  3. This formula uses the same “IF” logic as the previous formula =IF(G3=1,SUMIF(B:B,B3,N:N)/SUMIF(B:B,B3,H:H),"")
  4. Drag down formula
Picture
​Step 11: Clean up the data
Picture
  1. Paste values into separate workbook to decrease file size
  2. Filter results by column P or Q to eliminate blanks
  3. Hide unnecessary columns
  4. Paste values into separate worksheet resulting in one TTR % for each patient
ttr_calculation_documentation_2015-09-04.pdf
File Size: 611 kb
File Type: pdf
Download File


Comments are closed.

    Archives

    August 2020
    June 2019
    May 2017
    July 2016
    February 2016
    November 2015

    Categories

    All

    RSS Feed

Powered by Create your own unique website with customizable templates.