This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
Hmm I'm thinking you could use two multi row formula tools in a row that update the count field:
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!