Sum a running total that is grouped

Question

I have a workflow that I want to count received claim lines within 7 day periods specifically if 4 or more of these lines happen within a 7 day period. The issue is I want to count grouped by claimID but claims can run for a month+ and I'll need to identify, potentially, multiple periods of 7 days where there were 4 or more claim lines within the same claimID.

Does anyone have any ideas on how I could accomplish this?

I've tried multi-row formulas, but it's not working out. Maybe an iterative macro? I've never built one before but this seems like a potential use case.

DavidP · Answer

Hi @nordmaeo

This has been on my list to look at for a few days - sorry for taking so long!

Have a look at the attached workflow.

For a specific Claim ID, it finds the start and end date an then uses Generate Rows, Tile and a Join tool to find which day in the range each claim line falls in.

You can now use a multi-row formula to find sets of 4 or more claim lines that fall within 7 days.

It's not foolproof yet, but might nudge you in the right direction.

Have a play with it and let me know how we can tweak it.

find groups of claimlines.yxmd

nordmaeo · Answer

ClaimIDDateCount (field I need)4+ Claim lines (I need a field like this)Date+7Claim Lines5678901/1/2020101/8/202015678901/3/2020101/10/202025678901/7/2020111/14/202035678901/14/2020001/21/202045678901/20/2020101/27/202055678901/21/2020101/28/202065678901/22/2020101/29/202075678901/24/2020111/31/20208

Above is an example of what I want. The count field counts all of the claim lines  that fall within a single week. The 4+ counts how many times 4 or more claim lines fall within a week grouped by claimID. So basically whenever 4+ 1's in the count fall in a sequence that is within the same 7 days.

Let me know if I can add additional detail.

Thanks for your help!

atcodedog05 · Answer

Hi @nordmaeo

Can you please some sample data and expected out put. We will be happy to help 🙂