Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Multiple Sumif in Alteryx

Vic_S
5 - Atom

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! 

3 REPLIES 3
AshRez
8 - Asteroid

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.

Multiple Sumif in Alteryx1.PNG

 

 

Multiple Sumif in Alteryx2.PNG

 

 

Multiple Sumif in Alteryx3.PNG

 

 

 

Multiple Sumif in Alteryx4.PNG

Vic_S
5 - Atom

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!

ntakeda
12 - Quasar

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

  2025-02-10_09h18_28.png

 

  2. A priority table each items

  2025-02-10_09h20_45.png

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."

   2025-02-10_09h21_06.png

  •  Pattern 2: The output includes the word "Down."
  • d2cd68b4-5770-40c3-b96e-961b1d21d75e.png

If there are any discrepancies or if you have any questions, please let me know.

2025-02-10_09h39_11.png

Labels
Top Solution Authors