What I’m essentially looking to do is be able to track to see how many refills a patient had received in January, Feb through December. I have a list of what the pharmacy send. I would like to fill in column D and track any refilled scripts after their initial prescription fill.
Solved! Go to Solution.
Hi @Carolyne,
Thank you for your contribution to the Community!
To help make it easier for others to assist, it would be helpful to get some additional information on your question. Here's what we recommend you include in your reply:
Thank you very much!
DanM
Solution is attached.
First I used a date time tool to create a new column that would pull month which can then be grouped by. I would recommend using Month Year instead because I would assume this analysis spans more than just 2018.
Then I created a new column called count which acts as a placeholder for the next step.
Then I use running total, grouping by patient, and creating a running total of the count.
After creating the running total, I set the original count field to zero if the running total count field equals 1, because this would be the initial fill and you specified that you only wanted to look at subsequent refills.
Then I used a summarize tool to group by patient and month (you could group by Month Year if you choose) and then sum the count column. This will show you each patient's count of refills for every month. The initial fill is not included in this calculation because of the last step.
Finally I use a filter tool where refills > 0.
I hope this helps!
Thank you. This helped a lot.
Thank you so much for the quick response.
Follow-up Question: Based on the sample data below how would I exclude the 1st 2 fills (dated 1/6/2018) since the refill was canceled out. This is based on day supply being -90:In this case the patient would only have 1 refill dated 1/23/2018.
Patient | Refill Date | Drug Name | Month | Day Supply |
1 | 1/6/2018 | Drug A | January | 90 |
1 | 1/6/2018 | Drug A | February | -90 |
1 | 1/23/2018 | Drug A | March | 90 |
Hmm I'm thinking you could use two multi row formula tools in a row that update the count field:
IIF([Row+1:Day Supply]<0,0,[count])
IIF([Day Supply]<0,0,[count])
This would set the count to zero for both refill dates because the second row has a day supply less than zero. Can you give this a try and see if it accomplishes what you want? The only tricky piece would be if it was for the first fill so you wanted to set the next fill as the initial fill rather than a refill.
Also be sure to group by Patient in the multi-row tool!