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
Solved! Go to Solution.
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.
Thank you @apathetichell. looks like a viable solution.
I think I am not clear from the sample the first entry onwards.
Best
Pooja
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.
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.
This works. Thank you