Hi - I have two tables with the following information: Table 1- Customer NameTotal OrdersWeek OrderedCustomer17128/31/2020Customer1111/9/2020Customer1112/14/2020Customer1481/11/2021Customer21012/7/2020Customer336/1/2020Customer4251/4/2021Customer5238/17/2020Customer5109/7/2020Customer668/3/2020 Table 2- Customer NameTotal Orders ReceivedWeek ReceivedCustomer124011/9/2020Customer121/11/2021Customer181/11/2021Customer1181/11/2021Customer171/18/2021Customer141/18/2021Customer1601/25/2021Customer2112/28/2020Customer528/17/2020Customer518/24/2020Customer528/24/2020Customer528/24/2020Customer518/31/2020Customer529/7/2020Customer519/14/2020Customer539/14/2020Customer519/21/2020Customer529/21/2020Customer519/21/2020Customer519/28/2020Customer519/28/2020Customer5510/5/2020Customer5110/12/2020Customer628/3/2020Customer618/10/2020Customer618/10/2020 I want to use a First In First Out approach to know from which initial order the orders received are coming. The resulting table will show- Customer NameTotal Orders ReceivedWeek ReceivedWeek OrderedCustomer124011/9/20208/13/2020Customer121/11/20218/13/2020Customer181/11/20218/13/2020Customer1181/11/20218/13/2020Customer171/18/20218/13/2020Customer141/18/20218/13/2020Customer1211/25/20218/13/2020Customer111/25/202111/9/2020Customer111/25/202112/14/2020Customer1371/25/20211/11/2021Customer2112/28/202012/7/2020Customer528/17/20208/17/2020Customer518/24/20208/17/2020Customer528/24/20208/17/2020Customer528/24/20208/17/2020Customer518/31/20208/17/2020Customer529/7/20208/17/2020Customer519/14/20208/17/2020Customer539/14/20208/17/2020Customer519/21/20208/17/2020Customer529/21/20208/17/2020Customer519/21/20208/17/2020Customer519/28/20208/17/2020Customer519/28/20208/17/2020Customer5510/5/20208/17/2020Customer5110/12/20208/17/2020Customer628/3/20208/3/2020Customer618/10/20208/3/2020Customer618/10/20208/3/2020 The orders received are shown to come from the first order placed up until the total # of orders. Once the initial order amount is exceeded, the orders received come from the date of the next order placed, and so on and so forth. For example, the 60 orders received by Customer 1 on 1/25/21 will come from the order placed on 8/31/20 up until 300 orders in total have been received. The remaining orders received are then split accordingly up until the total order amount over the orders placed on 11/9/20, 12/14/20, and 1/11/21. Please let me know if you can provide direction as to how to solve in Alteryx. I've played around with the multirow formula but haven't exactly gotten it to work. Thanks!

First In First Out Calculation

Hi -

I have two tables with the following information:

Table 1-

Customer Name	Total Orders	Week Ordered
Customer1	712	8/31/2020
Customer1	1	11/9/2020
Customer1	1	12/14/2020
Customer1	48	1/11/2021
Customer2	10	12/7/2020
Customer3	3	6/1/2020
Customer4	25	1/4/2021
Customer5	23	8/17/2020
Customer5	10	9/7/2020
Customer6	6	8/3/2020

Table 2-

Customer Name	Total Orders Received	Week Received
Customer1	240	11/9/2020
Customer1	2	1/11/2021
Customer1	8	1/11/2021
Customer1	18	1/11/2021
Customer1	7	1/18/2021
Customer1	4	1/18/2021
Customer1	60	1/25/2021
Customer2	1	12/28/2020
Customer5	2	8/17/2020
Customer5	1	8/24/2020
Customer5	2	8/24/2020
Customer5	2	8/24/2020
Customer5	1	8/31/2020
Customer5	2	9/7/2020
Customer5	1	9/14/2020
Customer5	3	9/14/2020
Customer5	1	9/21/2020
Customer5	2	9/21/2020
Customer5	1	9/21/2020
Customer5	1	9/28/2020
Customer5	1	9/28/2020
Customer5	5	10/5/2020
Customer5	1	10/12/2020
Customer6	2	8/3/2020
Customer6	1	8/10/2020
Customer6	1	8/10/2020

I want to use a First In First Out approach to know from which initial order the orders received are coming.

The resulting table will show-

Customer Name	Total Orders Received	Week Received	Week Ordered
Customer1	240	11/9/2020	8/13/2020
Customer1	2	1/11/2021	8/13/2020
Customer1	8	1/11/2021	8/13/2020
Customer1	18	1/11/2021	8/13/2020
Customer1	7	1/18/2021	8/13/2020
Customer1	4	1/18/2021	8/13/2020
Customer1	21	1/25/2021	8/13/2020
Customer1	1	1/25/2021	11/9/2020
Customer1	1	1/25/2021	12/14/2020
Customer1	37	1/25/2021	1/11/2021
Customer2	1	12/28/2020	12/7/2020
Customer5	2	8/17/2020	8/17/2020
Customer5	1	8/24/2020	8/17/2020
Customer5	2	8/24/2020	8/17/2020
Customer5	2	8/24/2020	8/17/2020
Customer5	1	8/31/2020	8/17/2020
Customer5	2	9/7/2020	8/17/2020
Customer5	1	9/14/2020	8/17/2020
Customer5	3	9/14/2020	8/17/2020
Customer5	1	9/21/2020	8/17/2020
Customer5	2	9/21/2020	8/17/2020
Customer5	1	9/21/2020	8/17/2020
Customer5	1	9/28/2020	8/17/2020
Customer5	1	9/28/2020	8/17/2020
Customer5	5	10/5/2020	8/17/2020
Customer5	1	10/12/2020	8/17/2020
Customer6	2	8/3/2020	8/3/2020
Customer6	1	8/10/2020	8/3/2020
Customer6	1	8/10/2020	8/3/2020

The orders received are shown to come from the first order placed up until the total # of orders. Once the initial order amount is exceeded, the orders received come from the date of the next order placed, and so on and so forth. For example, the 60 orders received by Customer 1 on 1/25/21 will come from the order placed on 8/31/20 up until 300 orders in total have been received. The remaining orders received are then split accordingly up until the total order amount over the orders placed on 11/9/20, 12/14/20, and 1/11/21.

Please let me know if you can provide direction as to how to solve in Alteryx. I've played around with the multirow formula but haven't exactly gotten it to work.

Thanks!

Datasets

Output

Interface Tools

Time Series

Accepted answers

demand_james

Hi -

This is a pretty common problem, and its reasonably complex to solve. If you dont need to allocate remainders than you can use the multi-row formula. If you need to get an exact figure and split orders you have to use an iterative macro. Below is a post with lots of good detail on how to do this.

FIFO Logic - Iterative Macro - Alteryx Community

The iterative macros can also get troublesome if you start having lots of data (100,000s or millions of rows). However it works well for smaller applications.

Let me know if this helps or if you get further stuck

James

All comments

demand_james

Hi -

FIFO Logic - Iterative Macro - Alteryx Community

The iterative macros can also get troublesome if you start having lots of data (100,000s or millions of rows). However it works well for smaller applications.

Let me know if this helps or if you get further stuck

James

jvalcareng002

Thanks James - this was helpful. I was able to get it as far as shown in the attached workflow. However, I have 2 questions:

for

1) How can I get this to show only the Week Ordered for that order amount? Currently it shows multiple Week Ordered records for a single Order Received:

2) The Join is tool is currently joined on customer name. This appears to work well for this set of sample data. However, the data set I am adapting this for has thousands of records and is taking a long time run in order to match the customer names between the two sets. Do you have any recommendations to shortcut this process?

FIFO_Sample_Workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703