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!

[WilliamR]

Hi @PNiccolo ,

regarding your SUMIFS question, you can find a way to achieve this in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-use-If-statement-with-Sumifs-in...

(If this post helps, then please consider Accept it as the solution to help the other members find it more quickly).

[JoeS]

Hi @PNiccolo 

It'd be great if you could upload some sample data that's a bit bigger than the bits you have above, as I am sure there are a few more intricacies when the volume grows.

Is that something you can do?

I believe you are going to need to use a multi-row formula to achieve what you are looking for and then a cross tab to get the final format, but most likely other tools in between, so it'd be great if you can upload a sample and then I can build an example.

[PNiccolo]

Hi Joe!

Thank you for asking. 
please find attached the sample data. 

[danilang]

Hi @PNiccolo 

Why is your first value in AvgConsumptionLeadTime table in the 29/09/2018 column when the consumption occurred on 15/08/2018?

Dan

[PNiccolo]

Hi Dan!

thanks for asking.

the reason is that the transaction occurred on the 15/08 and not before.

the formula counts the transaction if is < (and not <=) that date

Nic

[danilang]

Hi @PNiccolo 

If that's the case, you'll never have any items in the first Lead period, since it's = the min value of all input dates.  Why include this period at all?

Dan 

[PNiccolo]

Hi Dan 

the idea is to have that cell fixed so I can calculate all the others using the lead time

Nic

[danilang · Accepted solution]

@PNiccolo 

Here's what I've come up with. 

It's completely dynamic and will handle a variable lead time.  The results for your original sample data are this.  The extra period at the end is there to compensate for the fact that the values are < and not <=.  If the last value in your data falls on the first day of the new lead time period, this will pick it up  

The data in your excel file generates 39 period columns starting from the start date.  Some the values in your excel have "No Value" instead of an actual value so these are not included.

Dan

[PNiccolo]

Hi Dan!
Thanks for your great work! I believe this is the solution I was looking for

I tried to replicate the workflow step by step to understand it better but I believe I miss the comprehension of a couple of tools/formulas. 
I leave my questions here, if you would like to help me clarifying them:

At this point you added this formula DateTimeAdd([MinDate],CEIL([TimeSpan]/[Lead Time])*[Lead Time],"days"), can you clarify what is the result of it?

Can you clarify the role of this tool?

I encountered some difficulties in understanding this Multi-field formula: 

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

Can you help me understanding it better?


Thank you very much in advance for all the help! This exercise is proving to be very insightful for me

Nic