Row level sum

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

Datasets

Reporting

Output

Common Use Cases

Help

Accepted answers

apathetichell

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.

max year.yxmd

All comments

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703