Forum Discussion
Search criteria in a field to evaluate if an event happened within 30 days pre and post
Hi:
I've found myself needing this formula on more than one occasion, have thought about it extensively, and appreciate any help I can get.
NOTES:
- 1 MRN can have multiple encounters; below are two examples.
- The goal is to evaluate at the MRN level and place an indicator in a separate column that flags to me that the MRN qualifies as meeting the Criteria. I don't need to necessarily know which lines within the MRN are the qualifiers (thought that would be a bonus), I just need to know that the MRN qualifies in general as meeting the criteria.
- Criteria: Within a given MRN, was an administered chemotherapy CPT given both Within 30 days prior to the office visit CPT AND Within 30 days after the office visit CPT.
- In example 1 below, MRN 123456 meets the criteria because chemo was administered 15 days before (on 2/18/17) and 25 days after (3/30/17) of the office visit on 3/5/17. Example 2 doesn't qualify since no combination of office visits and administered chemo happen with 30 days before or within 30 days after an office visit.
| MRN | ENCOUNTER | SERVICE DATE | CPT | CPT Description | Indicator | |
| Example 1 | 123456 | 34561234 | 6/7/2017 | 51720 | Administered Chemo | |
| 123456 | 45687922 | 3/5/2017 | 99201 | Office Visit 10 Minutes | Y | |
| 123456 | 34561234 | 3/30/2017 | 96420 | Administered Chemo | Y | |
| 123456 | 74531021 | 2/18/2017 | 96423 | Administered Chemo | Y | |
| 123456 | 47892643 | 7/17/2017 | 99214 | Office visit 25 Minutes | Y | |
| 123456 | 35715955 | 8/31/2017 | 96459 | Administered Chemo | Y | |
| Example 2 | 789100 | 41135122 | 3/4/2017 | 99215 | Office visit 40 Minutes | N |
| 789100 | 21122122 | 1/23/2017 | 96401 | Administered Chemo | N | |
| 789100 | 31555555 | 7/1/2017 | 99205 | Office visit 60 Minutes | N | |
| 789100 | 64588291 | 4/13/2017 | 96440 | Administered Chemo | N | |
| 789100 | 87654329 | 1/13/2017 | 96549 | Administered Chemo | N | |
| 789100 | 78956489 | 9/19/2017 | 99212 | Office visit 10 Minutes | N |
Thanks,
Chris
3 Replies
I /think/ this is what you want.. I've used a cpl IFs and a couple COUNTIFSs to at least tell you if the criteria is met.. the formula looks like this:
(I used a table so the formula is also table references)
=IF(LEFT([@[CPT Description]],2) = "Of",
IF(
AND((COUNTIFS([MRN],[@MRN],[SERVICE DATE],">"&[@[SERVICE DATE]]-30,[SERVICE DATE],"<"&[@[SERVICE DATE]],[CPT Description],"=Administered Chemo")),
(COUNTIFS([MRN],[@MRN],[SERVICE DATE],"<"&[@[SERVICE DATE]]+30,[SERVICE DATE],">"&[@[SERVICE DATE]],[CPT Description],"=Administered Chemo"))),
TRUE,FALSE),
"")It works something like.. if the description is an office visit - ie it starts with the letters 'Of' - then carry on, else leave the cell blank. I did this as from your description it sounded like you were only interested in pre/post office visits so I thought this was neater. Just be aware that this formula is looking for the CPT description to either start 'Office' or be 'Administered Chemo'.
If it is a visit then we are into another IF. This time we check to see if both of our COUNTIFS statements are larger than 0. The first COUNTIFS checks that MRN is the same as the row we are interested in, then that the service date is greater than the visit date - 30, then it checks that it is less than the visit date and finally that the description is 'Administered Chemo'. If this counts anything other than 0, we must have at least one Chemo appointment that meets the criteria.
Then the second COUNTIFS does the same thing but this time for service date is less than + 30 days.
If both conditions are meet then it returns True and your criteria are met, if it returns False then one or other criteria were not met.
I'm attaching my copy of the spreadsheet I was working on. I've added some extra columns that you can just delete as you see fit. They are:
'30 days before' - This is just the COUNTIFS for the before date on its own to make it easier to understand
'30 days after' - same as above but for after
'Indicator' - the combined formula as described above
'Indicator 2' - an alternative version of the formula that doesn't check the type of CPT. ie in this COUNTIFS we no longer care if it was an office visit or a chemo visit, as long as they were seen within the pre/post 30day criteria. (i wasn't sure which was right)
On your example you have a Y next to the 17/7/18 visit, but unless I'm being a bit daft, and none of the above works, that should be false. There isn't a chemo session within 30days either before or after.
Hopefully that works for you.
Thanks, Phillip! I appreciate your patience while I looked into this. I do believe this is working. I understand you used a table so the formula is a table reference, which is the only way I could get this to work. I also understand that the formula is looking for the CPT description to start with "Office" or "Administered Chemo."
Question: Since the example provided was a short sample size, I wanted to confirm whether the resulting value will be the number of qualified instances that meet the condition, correct? The resulting value can be something other than "0" or "1," right?
Also, much of what we do is based on values as opposed to descriptions. For future application of this formula, if CPTs 99201-99205 and 99211-99215 are office visits, and 96401-96549 are administered chemo, how would the formula have to be altered to incorporate the vlookup(s)?
Thanks so much for your help on this; I was wracking my brain on this one.
Best,
Chris
Hia,
I'm glad that we are making progress :)
Yes, the formula is counting the number of occurrences before and after that meet the criteria, so anything more than 0 is a match. The two columns that show the counting are only there so you can see more easily what is happening in larger the formula. You can delete them if you like, they aren't needed.
So Indicator and Indicator 2 are basically the same thing, they differ in that Indicator 2 doesn't care what sort of visit happened in the 30days, any entry in your table will do. Indicator makes a distinction between an office visit and an chemo session. In your example this was easy as it was just a case of looking for the words 'Administered Chemo'. If in the live version you use something else, then you can just substitute the words Administered Chemo for that something else. So it could be "=96401-96549".
It gets slightly harder if you have several criteria for what might be a chemo visit.
