Flag records where date spans based on multiple rows meet a specific grouping criteria

I’ve attached some fictional data with the desired output.

Bottom-line is I want to flag prescriptions that are being refilled 3 or more times in a 30 day period for the same medicine, same strength for a unique recipient.

Recipients try to fill their prescriptions many times or a few times over various periods. The flow should call out any that are part of 3 or more fills of the same strength within a 30 day stretch.

Thank you Alteryx Community for any guidance you might provide.

Demo_mutiple_pharma_fills.xlsx

Expression

Data Investigation

Time Series

Accepted answers

atcodedog05

Hi @StickData

Here is how you can do it.

Workflow:

I am checking 3 consecutive days for each medicine, strength and unique recipient. If its within 30day mark them as "Y"

I have same concern as @L_T those rows have only 2 times fill yet has been marked as Y

Hope this helps : )

3 times fill.yxmd

All comments

L_T

Hi @StickData,

Would the attached work for you?

Multiple pharma fills.yxmd

StickData

It does not.

I probably should have been more clear. The algorithm should flag only those prescriptions where they were filled 3 or more times within a 30 day window.

For example in the case of Jose Martin using the medicine "CellVitaminP", although he did fill the prescription 3 times, they occurred over a 66 day window, so none of these records should be flagged.

Records should be flagged if they are within a 30 days window with at least 2 other fills occupying the same 30 day window. Does that make sense? Thanks for trying solutions.

I've attached a file with the second tab showing what the results from Alteryx should be.

Suggestions welcomed

Demo_mutiple_pharma_fills_v2.xlsx

L_T

I am a bit confused - the output desired tab shows the same flags as the Alteryx workflow.
The only difference is in the case of Shawnese Tone, where you flagged these two lines as "Y":

GA689XX2R	800MG	15/12/2019	Y
GA689XX2R	800MG	18/12/2019	Y

However, there have only been 2 re-fills in a 30-days period so not sure why these should be treated differently from the others.

atcodedog05

Hi @StickData

Here is how you can do it.

Workflow:

I am checking 3 consecutive days for each medicine, strength and unique recipient. If its within 30day mark them as "Y"

I have same concern as @L_T those rows have only 2 times fill yet has been marked as Y

Hope this helps : )

3 times fill.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703