Row level sum
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Common Use Cases
- Datasets
- Help
- Output
- Reporting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @apathetichell. looks like a viable solution.
I think I am not clear from the sample the first entry onwards.
Best
Pooja
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sample data is attached.
Best
Pooja
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This works. Thank you