Complex (?) Excel Data Modelling in Alteryx

[PNiccolo]

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!

[danilang]

Hi @PNiccolo

DateTimeAdd([MinDate],CEIL([Span]/[LeadTime])*[LeadTime],"days") 

In this formula what I'm doing is setting the last possible date.  

CEIL([Span]/[LeadTime]) divides the [Span] (Difference between the first date) by the lead time and rounds it up to the next integer, so that you don't have partial periods.  Multiply this by [LeadTIme] to get the number of days until the last day of the last possible period.  This is the number of days that the DateTimeAdd function adds to [MinDate] to give you a new MaxDate.

This is the Generate Rows tool.  It generates a row for each date from [MinDate] to [MaxDate].  This is so we can generate every possible combination of Country/Plant/Item/Date in the next Append fields tool.

if Mod(DatetimeDiff([Date],[MinDate],"days"),[LeadTime])=0 then
	DatetimeAdd([Date],[LeadTime],"days")
else
	[Row-1:Period]
Endif

Sorry.  I left the commented code in.  The comment is the part between /* and */.  You can delete this completely.   

This formula calculates which period all the different dates fall in. The condition is Mod(DatetimeDiff([Date],[MinDate],"days"),[LeadTime])=0.   The green part calculates the number of days since [MinDate].  The blue part uses the Mod(x,y) function the return only the remainder of x/y.  If this remainder is 0 then this day is start of a new period so generate the next period value.  If the remainder is not 0 then use the Period value from the previous row.   

Hope this clears things up.

Dan

[PNiccolo]

This is very helpful! Thanks a lot!