Frequency count

[Carolyne]

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.

[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!

[Carolyne · Accepted solution]

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

[bbak]

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!