Row level sum

[Analytics_Pooja]

Hi Alteryx Junkies

Although I am not new to Alteryx, but still I am having a difficulty in solving this problem. 

I have a dataset with Year over year accumulated amount. I want to add accumulated amount until it reaches the paid amount (equal or less than but exceed) and find out the start year and end year from the Effective Year. Here is the screenshot of the sample problem. 

For example for cust_id 90132, the accumulated Amount starting from 2021 to 2016 adds up to make paid amount as $2021.85. Therefore the start year is 2016 and the end year is 2021. The output looks like this: 

The solution might involve summing up Accumulated Amount until the Amount matches to the Paid Amount and then pull Start year and End Year from Effective Year.

Any help would be greatly appreciated. 

Best

Pooja 

[apathetichell]

without creating a workflow from sample data:

running total/group by customer id.  filter where running total exceeds paid amount. sample the first entry sorted date descended. group by customer id and summarize max year. attach that to group by customer id and summarize min year from beginning dataset. Join start year and end year back to dataset.

[Analytics_Pooja]

Thank you @apathetichell.  looks like a viable solution.

I think I am not clear from the sample the first entry onwards. 

Best

Pooja 

[apathetichell]

Pooja - post a sample of data so I can show you how I'd do it.

You are looking for the first instance of a criteria being met for each group (ie running total>paid amount) - you don't need to to stop running total. once you find the first instance you can extract the first year that it occurs via summarize/date after filtering.

[Analytics_Pooja]

Sample data is attached. 

Best

Pooja 

[apathetichell]

I retyped the screengrab data - looks like the sum is fifty cents off to get 90132 to match for 2016.

[Analytics_Pooja]

Thanks @apathetichell 

That's awesome. That's fine - the sum need not to be exact.

I think the confusion is - I am looking for both Start Date and End date. That's why sample first row might not work in this situation.  For example for Cust_id 90134 the start year would be 2017 and end year would be 2018 not 2021 as 2021 Accum Amount is zero. 

I hope this helps.

[apathetichell · Accepted solution]

That means you are ignoring the 2021 0 year for 90134. If that is not the case - delete the filter below the lower summarize tool.

There is a rounding error for 90132. The sum is slightly below your amount for 2016 ($.50) so it doesn't trigger until 2015.

[Analytics_Pooja]

This works. Thank you