Hi everyone!
Today I am trying to replicate a model I have already created in Excel with Alteryx.
The model seems a bit complex, or at least a bit intricate, and since I might have to replicate it other times with other data I would like to build it on Alteryx making it as dynamic as possible.
Sorry in advance for the long post, I hope you want to help me in this challenge!
The final goal of this exercise is to calculate the Min and Max level of each item to be kept in the plant warehouse.
So I have a table that looks like this (Name = Transactions Table)
| | A | B | C | D | E |
| 1 | Country | Plant | Plant-Item Code | Transaction Date | Transaction Quantity |
| 2 | Nigeria | A | NigeriaA-15060500.01.11.00421 | 15/08/2018 | -2 |
| 3 | Nigeria | A | NigeriaA-15060500.01.11.00421 | 29/10/2018 | -1 |
| 4 | Nigeria | B | NigeriaB-15060500.01.11.00110 | 02/10/2018 | 4 |
| 5 | Nigeria | C | NigeriaC-15060500.01.11.00123 | 02/10/2018 | -4 |
| 6 | Nigeria | X | NigeriaX-15060500.01.11.00534 | 07/09/2018 | 17 |
This table represents all the transactions happened in various plants (if the transaction quantity is < 0 it means it is a consumption).
The other inputs I have are:
- Lead Time (hard coded) --> number of days between the order of the item and the delivery date
- Number of periods (calculated) --> number of times the lead time is repeated during the time span (last transaction date - first transaction date)
- Max Factor (hard coded) --> needed to calculate the Max once I have the Min.
The first thing I need to do is to calculate the average consumption during the lead time.
Therefore, I assume a lead time of 45 days and calculate the number of periods:
- First transaction date = 15/08/2018
- Last transaction date = 29/10/2018
- N. of periods = 2 (75 days divided by 45 days = 1.67 periods of 45 days)
To do so I created a table that looks like this (Name = AvgConsumptionLeadTime)
| | A | B | C | D |
| 1 | Plant-Item Code | 15/08/2018 | 29/09/2018 | 13/11/2018 |
| 2 | NigeriaA-15060500.01.11.00421 | 0 | 2 | 1 |
| 3 | NigeriaB-15060500.01.11.00110 | 0 | 0 | 0 |
| 4 | NigeriaC-15060500.01.11.00123 | 0 | 0 | 4 |
| 5 | NigeriaX-15060500.01.11.00534 | 0 | 0 | 0 |
First challenge: the number of columns of this table needs to change depending on the number of periods. If I change the lead time (e.g. 30 days or 60 days) I should have more colums to represent the higher (or lower) number of periods in the same time span.
Now that I have this table, I have to populate its cells:
In each cell there should be the overall quantity consumed in the period.
To do so, in Excel I used a rather intricate SUMIFS that you can find here for the cell C2:
=SUMIFS(TransactionsTable!$E$2:$E$6;TransactionsTable!$C$2:$C$6;'AvgConsumptionLeadTime'!$A2;TransactionsTable!$D$2:$D$6;"<"&C$1;TransactionsTable!$F$2:$F$34910;"<"&0)-B3)
What this formula does is to sum all the transactions (TransactionsTable!$E$2:$E$6) that:
- Are referred to that Plant-item (TransactionsTable!$C$2:$C$6;'AvgConsumptionLeadTime'!$A2)
- Happened within the period (TransactionsTable!$D$2:$D$6;"<"&C$1)
- Are actual consumptions (TransactionsTable!$F$2:$F$34910;"<"&0)
- Excluding the consumptions already counted in the previous periods (-B3)
The results of this formula are in blue in the table.
Second challenge: how do I replicate in Alteryx this SUMIFS?
I believe my post is already extremely long so I will stop here and hope my explanation was sufficently clear to get your precious help.
Thank you very very much in advance for any contribution you want to give! Your advices will surely help me in progressing with Alteryx!
Ciao!
