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

[StickData]

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.

[L_T]

Hi @StickData,

Would the attached work for you?

[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

[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 · Accepted solution]

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 : )

[StickData]

Hi L_T,  thanks for your good work.  You spotted a case of me supplying bad fictional data.  Shawnese's other January dates should have been 2020.  Good catch.  @atcodedog05 has come up with a working solution

[StickData]

Nice work! This solution works.  I did supply some bad data.  Shawnese's January dates should have been 2020.  Thanks for showing the power of the multi-row forumula tool

[atcodedog05]

Happy to help : ) @StickData 

Cheers and have a nice day!