Complex Multi-Row IF Formula

Hi,

Please see the description below and accompanying screenshot.

I am looking for a complex multi-row IF formula:

Objective

When the purchase cost is NULL (i.e. for [TYPE] = "SALE") I want it to bring in the Purchase Cost of the first mathematically possible PURCHASE.

For example, in Records #1 and #2, I'd want the Purchase Cost from record #1 brought down into both cells. However, for record #9, I would like the Purchase Cost from record 4 brought down.

I'd like alteryx to recognise the first Purchase that the Sale could relate to i.e. the first sale of -72 (record #2) must be sold from the original purchase of 114 (record #1). Similarly, the second sale (-42 in record #3) has to come from the purchase of 114 (record #1). However, the sale of 75 (record #9) originates from the purchase of 171 units (record 4) NOT the purchase of 300 units (record #8).

If possible, I'd also like this to take account of the possible FUTURE scenario where I sell 300 units. I would need the purchase cost within the SALE row to display the sum of the purchase costs where the quantity sums to 200. i.e. it would need to factor in records #4, #5 and #6 (quantities 171, 2 and 35 respectively.

I have provided an Excel workbook which displays my ideal output (including the hypothetical sale).

Thank you so much in advance.

Jamie

Alteryx Support - Multi-row Formula.xlsx

Help

Expression

Accepted answers

DavidP

This is an interesting problem called the FIFO (first in first out) principle. There are 2 ways to solve this problem.

1. Build an iterative macro

2. Split sales and purchases into rows with quantity of 1 and then use the date for each to ensure that every sales transaction uses the oldest purchases first.

There is a previous post on the community that deals with a similar problem and @reply_mueller built an iterative macro while I used the 2nd method. Both methods give you the desired outcome. Here is the link to that post

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-products/td-p/528612/page/3

I again used method 2 here to solve your problem. Yours is a slightly simpler version of the problem since you only have one "product", but the solution can actually handle multiple products correctly. "Product" in this case is Player Name.

It will correctly deal with Sales from multiple purchase batches.

Have a look and please let me know if you have any questions.

FIFO principle to calculate Purchase Cost.yxmd

DavidP

Hi Jamie,

Try attached version. The 1st join tool should join on Player Name and Tile Sequence, the 2nd Join tool should join on Player Name and Date.

The Tile tools should be set to Unique Value on Player Name

Could you attach some multi-player data for me to double check.

FIFO principle to calculate Purchase Cost.yxmd

All comments

DavidP

This is an interesting problem called the FIFO (first in first out) principle. There are 2 ways to solve this problem.

1. Build an iterative macro

2. Split sales and purchases into rows with quantity of 1 and then use the date for each to ensure that every sales transaction uses the oldest purchases first.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-products/td-p/528612/page/3

It will correctly deal with Sales from multiple purchase batches.

Have a look and please let me know if you have any questions.

FIFO principle to calculate Purchase Cost.yxmd

jamieeee

Wow, thank you so much David! Really appreciate that!

I need a bit of time to look through it but this looks like a great solution. I will let you know if I have any questions.

Jamie

jamieeee

Hi @DavidP

Thanks again for your help. I worked through your solution last night and it works really well, thanks!

I do have one follow up question, if I may.

In my example, I had filtered my data to only display one player (Jadon Sancho). However, now that we have a solution, I am trying to apply the same logic across the full data set (i.e. multiple players all with multiple purchase and sell orders).

When I remove the player filter, the logic in the workflow seems to break and I am getting incorrect calculated purchase costs. It seems like this is because the join of the SALE and PURCHASE tiles seems to join different players (even though they have the same tile sequence no.).

I have tried to adjust this join so that it joins on Tile Sequence No, Player Name and / or Tile Number but can't seem to get it to reconcile. I have a screenshot below (note that sales of non-Aaron Ramsey are being matched to Aaron Ramsey purchase orders).

I'm hoping this is a simple fix but let me know if you need me to provide more data in an Excel.

Thank you again!!

Jamie

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703