3 Admits within 12 Months?

[Bonediggler]

Experts - 

Here's the scenario: I have member ids and admit dates.  The admit dates may be in 2019, 2020 or both, and can be 1 or more per member.  I need to figure out which members had at least 3 admits within 12 months.

Your help in solving this problem is greatly appreciated!

[apathetichell · Accepted solution]

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/COUNTIFS-Dynamic-conditions/td-p/75261...

@danilang's workflow can be adjusted for your need. The condition on the mutli-row count is modified to make sure that the previous admits occurred within 12 months. The most straight forward way to do this would be expand the multi-row option 2 and check if datetimediff([date],[row-2:date],"years")<=1 and increment the count if is or reset the count if it isn't.

[Bonediggler]

That's a start I think, I will try it out.

But what is multi-row "option 2"? 

[apathetichell]

in multi-row formula there is a section which asks for how many rows you want to display. If you change that to 2 (from 1) you can access [row-2] - aka two rows before- as opposed to [row-1] only.  If you need more help  - post what you have and I can take a look.

[Bonediggler]

Actually the 2 row lookback doesn't work 100%.  Consider this scenario:

MemberID  AdmitDt

123            2019-01-03

123            2020-03-01

123            2020-03-02

123            2020-03-03

Only the last admit is counted, whereas the last 3 should be counted.  I came up with a workaround using another multi-row that groups on member and admit year but am wondering if there is a better way....?

[apathetichell]

Hi - no you are testing for the negative of the condition - it's not about finding what should count but rather what shouldn't count. Since item 1 is within the 12 months it won't trigger the reset. If it wasn't the reset would be triggered. Does that make sense? You trigger the reset when that check fails. Since the check isn't triggered it counts.

Thinking about it though - depending upon the number of rows this might be easier in a macro where you can have the time frames change more dynamically.

[apathetichell]

o.k. - so each entry should have the current date and the date 12 months back (datetimediff([date],-1,"years") then when fed into a macro you append the current entry (received via macro input) with the full data set (static entry in macro). You filter by date/item through [date] and the date time diff 1 year before (or whatever time length you are comparing). The you use summarize to count and macro-ouput the count/id/date.