Calculations with running subtotals
- 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
I am dealing with a challenge on how to translate Excel math to Alteryx.
My dataset has a City column, ID column (grouped), and a variance column. The target to calculate is the real 'variance to add' per station.
For all ID #1: the 'variance to add' is the variance as given.
For ID #2: the variance minus the 'variance to add' on row 1, but if this results in a negative number, the variance should be 0
For ID #3 the variance minus the sum of the 'variance to add' from ID 1 & 2, but if this results in a negative number, the 'variance to add' should be 0.
Essentially, it takes the variance of that record minus the running total of the 'variances to add'. If this results in a negative number, replace with 0.
Attached I have the Excel logic with formulas whereas the highlighted yellow column is the target output. Any idea on how to translate to Alteryx?
Solved! Go to Solution.
- Labels:
- Designer Cloud
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@catmar ,
Record ID #1: For each city, the Variance to Add column is the variance given.
Record ID #2: The Variance to Add column is the Variance minus the Variance to Add of the previous column unless the previous Variance to Add is greater than record #2's Variance amount.
Record ID #3: The Variance to Add column is the record #3's variance amount minus the sum of the previous two record's Variance to Add values.
I used "Helper Column" to maintain the running subtotal amount of the Variance to Add column so that we could adjust the actual Variance to Add column. We just de-select the "Helper Column" at the end of the workflow and you have your desired output. Also, in Excel, you can achieve this by freezing the top record of the Variance to Add column by putting dollar signs before J and right before the number which will be the first record of each city. Please see screenshot for DFW.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is amazing and perfect! Thank you so much!
