Complex Multi-Row IF Formula

[jamieeee]

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

[jamieeee]

Hi David, 

I have been so busy at work that I haven't had time to look at this, so apologies for that. 

Thank you again for your help. I have had a look through and I feel like we are so close to a solution but it requires a combination of the FIFO method and the cumulative cost. As you suggested, I have put my data into Excel and used formula / comments to describe the logic I am looking for. 

You will find 3x players data attached. Each of these players have different purchase / sale patterns but I am looking for the same end result. This is the green highlighted cell that represents the [current] portfolio cost of the player. 

Hopefully this helps but please let me know if you have any questions!! I am around all week so will be able to respond swiftly. 

Thank you,

Jamie

[jamieeee]

@DavidP no rush in responding but I forgot to tag you in my response, above. 

Thanks,

Jamie

[DavidP]

Hi @jamieeee 

Sorry for taking so long to get back to you. I had to find the time and head space to reevaluate this problem form the start.

The good news is that I found the flaw in my original logic.

When we reduce the purchases to individual rows with quantity of 1 (as per our fifo method) we have to calculate the unit purchase cost for each row by dividing purchase cost by quantity. Then when we summarize after the join, we should sum the individual unit costs to get the purchase cost for that sales transaction. 

What we've essentially been doing is to calculate an average purchase cost for every sale, which is incorrect.

I've highlighted the changes below that now produces the correct calculated purchase cost. This should fix calculated purchase cost for all players.

[DavidP]

Here's version 3 with Cost of Purchase/Sale  and Portfolio cost reflecting the numbers in you spreadsheet example.

[jamieeee]

@DavidP Thanks for getting back to me - no problem at all. I'm also struggling for time this week but have annual leave next week so will be able to devote some time to looking at this. 

Appreciate your help as always!

Jamie