Sum a running total that is grouped

[nordmaeo]

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.

[atcodedog05]

Hi @nordmaeo 

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

[nordmaeo]

ClaimID	Date	Count (field I need)	4+ Claim lines (I need a field like this)	Date+7	Claim Lines
567890	1/1/2020	1	0	1/8/2020	1
567890	1/3/2020	1	0	1/10/2020	2
567890	1/7/2020	1	1	1/14/2020	3
567890	1/14/2020	0	0	1/21/2020	4
567890	1/20/2020	1	0	1/27/2020	5
567890	1/21/2020	1	0	1/28/2020	6
567890	1/22/2020	1	0	1/29/2020	7
567890	1/24/2020	1	1	1/31/2020	8

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!

[DavidP]

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.