Subtracting total of duplicate rows from a different field
- 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'm working on converting some Excel data to Alteryx and have been having trouble with one of the formulas. I have two columns of values and the excel file is is using a sumif to compare the TOTAL of values in Value 2 to the value listed in Value 1 for two possible actions:
New Value Action #1: if value 1<value 2 then use value 1
New Value Action #2: if value 1<the total of multiple value 2 keys, the subtract total value 2 from value 1
I know my explanation is a bit confusing so let me know if you have any clarifying questions. Any help is greatly appreciated.
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you please explain how you are getting 50 in column D for row 5?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What the original formula is doing is subtracting Value 1 from the total of Value 2 and replacing the second entry with the result:
(Value 2 + Value 2) - Value 1
(200 + 100) - 250 = 50
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thanks!
Are you trying to execute below condition in the same column?
If value 1 < value 2 and value 1 < (value 1 + value 2), then (value 1 + value 2) - value 1
elseif If value 1 < value 2 and value 1 > (value 1 + value 2), value 1
else null?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Do you want the two "actions" to create separate columns or should both sets of rules be consolidated for a single new column.
If I understand your criteria correctly, the attached workflow should work for your purposes. I demonstrated both separate new columns and a consolidated one. Essentially, you want to find the total of column 2 and then add these values to each record for the comparison.
Let me know if that helps or if you need further adjustments/clarification.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@echuong1 So simple and it works!! Thank you! It makes me feel better that I was close to that but you pushed it over the finish line.
Thanks!!!