Frequency count

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.

Topic of Interest

Accepted answers

Carolyne

Thank you. This helped a lot.

All comments

DanM

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:

What Alteryx Version are you using?
Do you have a Workflow with sample data you can share?
What about a Screenshot?
What does the data look like? (Beginning data vs. Final Output)
How would one replicate the issue?
Did you receive an Error Message?

Thank you very much!
DanM

bbak

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!

Frequency Count Solution.yxzp

Carolyne

Thank you. This helped a lot.

Carolyne

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

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703