Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Sum a running total that is grouped

nordmaeo
7 - Meteor

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.

3 REPLIES 3
atcodedog05
22 - Nova
22 - Nova

Hi @nordmaeo 

 

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

nordmaeo
7 - Meteor
ClaimIDDateCount (field I need)4+ Claim lines (I need a field like this)Date+7Claim Lines
5678901/1/2020101/8/20201
5678901/3/2020101/10/20202
5678901/7/2020111/14/20203
5678901/14/2020001/21/20204
5678901/20/2020101/27/20205
5678901/21/2020101/28/20206
5678901/22/2020101/29/20207
5678901/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!

DavidP
17 - Castor
17 - Castor

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.

 

DavidP_0-1605798934273.png

 

Labels