FIFO Calculation with multiple products

[AustinLeung]

I am trying to calculate the cost of sales under First-in-first-out method. The cost could be calculated by excel, which is in column N in the "Results" file. However, I am not sure how I could use Alteryx to do the same thing.

Any help is much appreciated. Thanks in advance.

Cheers,

Austin

[AustinLeung]

@DavidP,

Thank you so much David! This approach is so easy to understand, especially to someone who is new to Alteryx.

I found a minor problem for this approach, when the transaction quantity is large, e.g. 1,000,000 units, it will take a longer time to generate the results.

But if the quantity is not large, this is definitely a perfect solution to calculate the FIFO cost of sales. Thanks again for your effort! I truly appreciate your help.

[JoeS]

@DavidP  I assume you meant to tag @JoeM  😉

[DavidP]

Correct, sorry @JoeS  so many Joe's...

[JoeM]

I'll pass it on to the team! Thanks for getting it to one of the Joes!

[Akyba_Lev]

@DavidP, thanks a lot!

The workflow is very helpful.

Could you please clarify one moment: what is "DateTimeDiff filter" for?

P.S: I tried to apply the workflow on my case, however, I guess, there is a problem with quantities with decimals. I tried to create rows with initialization expression 0.001, but it did not work out. Any ideas how to solve the problem?)

[DavidP]

@Akyba_Lev , the datetimediff filter is not really needed. 

This method is not designed with decimals in mind - if you can post some data and a description of what you'd like to achieve I can play around with it and see if I can help.

[jvalcareng002]

@reply_mueller - I've been searching these discussions for a solution to my FIFO problem, and your solution here seems to be the closest I could find to my case.

I have been trying to find a way to solve this without using an iterative macro since that is above my current experience, but since I am working with thousands of lines of data I think it will be the only way to solve. I was hoping you (or someone in this feed) would be able to advise. 

I have two tables with the following data a) Orders Created and b) Orders Received:

a) 

company_name_original	Order Created Date	Orders
Customer1	12/11/2020	10
Customer10	11/6/2020	4
Customer10	12/8/2020	6
Customer10	12/10/2020	4
Customer10	12/11/2020	50
Customer10	12/14/2020	25
Customer10	12/23/2020	35
Customer10	1/14/2021	5
Customer100	12/9/2020	5

b)

company_name_original	Order Received Date	Orders Received
Customer1	12/28/2020	1
Customer10	12/8/2020	4
Customer10	12/11/2020	1
Customer10	12/12/2020	4
Customer10	12/24/2020	1
Customer10	1/11/2021	1
Customer10	1/13/2021	1
Customer100	12/14/2020	2
Customer100	12/15/2020	3

I would like the result to look something like the table below, where orders received is matched to orders created on a first in first out basis. That is orders received are shown to come from the first order placed up until the total # order amount. Once the initial order amount is exceeded, the orders received come from the next order created, and so on and so forth. For example, if Customer 1 orders 10 units on Jan-1 and 10 units on Jan-2, then when they receive 15 units on Jan-3, 10 of those units will be from the order created Jan-1 and 5 will be from the order created Jan-2. The remaining orders received are then attributed accordingly up until the total order amount for each order created.

Result:

company_name_original	Order Received Date	Orders Received	Order Created Date
Customer1	12/28/2020	1	12/11/2020
Customer10	12/8/2020	4	11/6/2020
Customer10	12/11/2020	1	12/8/2020
Customer10	12/12/2020	4	12/8/2020
Customer10	12/24/2020	1	12/8/2020
Customer10	1/11/2021	1	12/10/2020
Customer10	1/13/2021	1	12/10/2020
Customer100	12/14/2020	2	12/9/2020
Customer100	12/15/2020	3	12/9/2020

Due to the large data set, I have looked into grouping the orders by week created / week received rather than exact date. I have attached the sample data below and would appreciate any help you can offer. My biggest issue has been splitting the amount of the order received between several created orders when the amount received exceeds the total amount available in the previous order (i.e. as explained in the example above).

Thanks,

Juliana