Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Complex Multi-Row IF Formula

jamieeee
7 - Meteor

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. 

 

jamieeee_0-1591521199043.png

 

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

 

Thank you so much in advance. 

Jamie

14 REPLIES 14
DavidP
17 - Castor
17 - Castor

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-product...

 

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.

 

 

DavidP_0-1591617684938.png

 

jamieeee
7 - Meteor

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
7 - Meteor

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. 

 

jamieeee_0-1591691132018.png

 

Thank you again!!

Jamie

DavidP
17 - Castor
17 - Castor

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.

jamieeee
7 - Meteor

Thank you @DavidP , that worked again!

 

However, I have now identified another issue... I really hope you don't mind me asking. Absolutely no rush in replying. 

 

Earlier in my workflow, I had calculated the 'Original Purchase Quantity' using the multi-row IF formulas displayed in the screenshot below. Upon review, this formula is not pulling through the correct 'Original Purchase Quantity' as can be seen in the SALE records below. The original purchase quantity then feeds through into the workflow that you have already helped me with. 

 

The reason I think this is the issue is because the Purchase Cost for Paul Pogba that I am trying to reconcile to equates to 1,622 whereas I am getting 5,635

 

I have been attempting to use the 'generate rows' and 'tile' tools to solve this problem (i.e. to identify the correct Original Purchase Quantity for SALEs) but have had no luck yet. Do you have any suggestions?

 

I attach the paul pogba data and my workflow for your reference.  

 

jamieeee_0-1591723554057.png

 

DavidP
17 - Castor
17 - Castor

Hi Jamie,

 

Helping out with problems is what we're here for on the community so fire away with the questions.

 

Can you put into words what you want the logic for the the original purchase quantity to be? Also, can you provide a sample input file for your workflow, then I'll be able to troubleshoot better.

jamieeee
7 - Meteor

Thanks David. 

 

I attached my workflow with a sample input file "PP Data.xlsx" in the above post but do you mean the raw data? I have attached an Excel version of the PP Data in case but let me know if I have misunderstood.

 

I guess my overall aim here to calculate the purchase cost of the players currently in my portfolio. I have created a workflow that calculates the purchase cost for players that I have purchased just once and have not sold (i.e. they remain in my portfolio). You then helped me with the logic to calculate the purchase cost of some of the tricker players where I have bought and sold the player numerous times (but are now currently in my portfolio).

 

Paul Pogba is another of these players that falls under this 'bought and sold multiple times' category. For some reason, the logic that worked with Jadon Sancho (player you previously helped with) didn't work with Paul Pogba and I believe it is because my 'Original Purchase Quantity' wasn't accurate for Paul Pogba. In the screenshot below, you can see that the 'Original Purchase Quantity' for Jadon Sancho SALEs correctly ties to the quantity originally purchased (i.e. the two sales of 72 and 42 have an original purchase quantity of 114).

 

However, if you refer back to my Paul Pogba screenshot above, you will see that because there were two PURCHASEs and then a SALE, the logic is slightly different and so my Multi-Row IF formula has not correctly identified the correct 'Original Purchase Quantity'. (e.g. the 'Original Purchase Quantity' for the first Paul Pogba sale is 132 but it should be the combined purchases of 132 and 27. 

 

The context behind the 'Original Purchase Quantity' is that I use this to calculate the current purchase cost of my portfolio (the overall goal). You will see in my 'PP Query' workflow that the penultimate tool (formula) calculates the pro-rated calculated purchase cost. This was one addition I made to your previous workflow support. This allows me to assign each sale it's own purchase cost (essentially the cost of sale) meaning that when I sum up the 'Purchase Cost' across the full player's data, I can calculate the overall purchase cost of that player in my portfolio. 

 

I hope this makes sense.. apologies for the long paragraphs. Happy to provide more data and/or more context. 

 

jamieeee_0-1591776298478.png

 

Thanks again,

Jamie

DavidP
17 - Castor
17 - Castor

Hi Jamie,

 

I'm wondering if what you're looking for is more a cumulative quantity, taking into account all previous purchase and sales transactions. Have a look at the new bit I added to your workflow to calculate cumulative quantity.

 

I also noticed that the formula for Pro Rate Sale will always yield null values for every sales row (since purchase cost is null). Have a look at my slightly different formula for New Pro Rate Sale.

 

If I'm still not understanding exactly how you want original purchase quantity or pro rate sale to be calculated, perhaps the easiest way to explain it to me is to create an excel file containing both players and then use excel formulas based on specific cells to show what you want the original purchase quantity and pro rate sale values to be for every row, then I can try and build the logic to achieve that.

 

DavidP_0-1591867652687.png

 

jamieeee
7 - Meteor

Hi @DavidP 

 

I seemed to have missed my email notification so apologies for the delayed response. I will take a look this weekend and come back to you. 

 

Appreciate your help!

Labels