Multiple Sumif in Alteryx
- 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
Hello, I am looking to create a table that displays the transportation volume for each warehouse under all possible scenarios that 1 or more warehouses are down. In Excel, I've used the SUMIFS function, but I have a large volume of data. I’ve tried running total tool and summary tool, but not working.
I have data for various warehouses and the items they transport to the store. The column “First/ Second/ Third” indicates the priority of each warehouse for product transportation. For instance, if Warehouse A is unavailable (i.e. Down), Warehouse C will be used to transport "Apple". If Warehouse A and C are Down, then Warehouse B will transport “Apple”.
My goal is to create a table that reflects the volume each warehouse would need to transport if other warehouses are unable to deliver goods due to bad weather. For example, if both Warehouse A and Warehouse C are out of operation simultaneously, I would like to know the transportation volume required from the remaining warehouses.
Sample Data I have:
Items | First | Second | Third | Volume |
Apple | Warehouse A | Warehouse C | Warehouse B | 100 |
Orange | Warehouse C | Warehouse A | Warehouse C | 150 |
Pineapple | Warehouse B | Warehouse A | Warehouse D | 200 |
I've included an updated sample table that I would like to achieve below:
If there are only 3 Warehouses, the possible combinations to consider are as follows. “XXX” means the total volume that the warehouse will have to transport:
Warehouse | Case 1 | Case 2 | Case 3 | Case 4 | Case 5 | Case 6 |
Warehouse A | Down | xxx | xxx | Down | xxx | Down |
Warehouse B | xxx | Down | xxx | Down | Down | xxx |
Warehouse C | xxx | xxx | Down | xxx | Down | Down |
Initially, I thought of using Excel's SUMIF function to achieve this, but with so many combinations to consider for over 10 warehouses, it's becoming a time-consuming task. I was wondering if Alteryx might have a more efficient approach to help me achieve this.
Appreciate in advance!
- Labels:
- Iterative Macro
- Macros
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If I understood your requirement correctly, You are trying to get for each Warehouse, the max volume if all other warehouse failed, is that right?
it wasn't clear what you meant with Case 1, 2, and 3.
but I would think you need something that convert Columns "First", "Second", "Third" into rows to be able to sum the total volume per warehouse.
It should be something like these screenshots.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello, apologize for any confusion. I'm aiming to create a table that visualizes the maximum volume for each warehouse in scenarios where one or more warehouses are unavailable. I have a large dataset covering over 10 warehouses, and I'd like to analyze various cases.
To break this down further, the table should display different scenarios. For instance, the table should illustrate the outcomes when Warehouse A is down, when both Warehouse A and B are down, and so forth
I've included an updated sample table that I would like to achieve below:
If there are only 3 Warehouses, the possible combinations to consider are as follows. “XXX” means the total volume that the warehouse will have to transport:
Warehouse | Case 1 | Case 2 | Case 3 | Case 4 | Case 5 | Case 6 |
Warehouse A | Down | xxx | xxx | Down | xxx | Down |
Warehouse B | xxx | Down | xxx | Down | Down | xxx |
Warehouse C | xxx | xxx | Down | xxx | Down | Down |
Initially, I thought of using Excel's SUMIF function to achieve this, but with so many combinations to consider for over 10 warehouses, it's becoming a time-consuming task. I was wondering if Alteryx might have a more efficient approach to help me achieve this.
Any advice or suggestions would be greatly appreciated.
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Vic_S
I have created a workflow. Please check the attached file.
I designed it as follows—does this meet the requirements?
The inputs are two tables:
1.A table with case and Down combinations
2. A priority table each items
The output consists of case and the total volume that the warehouse will have to transport.
I created two patterns:
- Pattern 1: The output does not include the word "Down."
- Pattern 2: The output includes the word "Down."
If there are any discrepancies or if you have any questions, please let me know.
