How can I state the purchase cost of units held in current portfolio?

[jamieeee]

Hi, 

I would really appreciate your help with this complex problem.

Objective

I am trying to state the purchase cost of the players that are currently in my portfolio based off a purchase order listing. I am having trouble doing this is because the units I currently hold in some players were bought across multiple purchase orders. I am not sure which processes / tools / formulae i can use to achieve this.

The first screenshot below is a subset listing of ALL purchase orders for the players currently in my portfolio. However, as you can see, the quantity purchased doesn't always tie to the units currently in my portfolio (Current Portfolio Q) for each player. This is because some units were sold and then re-purchased, for example. 

I have listed below three examples of why I have been unable to state the current purchase cost of my portfolio. 

Examples:

1) Adama (records #1 and #2) - In my current portfolio, I hold 100 units of Adama. However, I previously owned 100 units of Adama (on 01/09/2019) but then sold those 100 units. I then decided to re-purchase 100 Adama units on 27/05/2020 which now sit in my portfolio. Hence, the purchase cost for my current portfolio should be 310 and NOT 94. The 94 should be ignored.  

2) Bruno Fernandes (records #5 and #6) - I currently hold 111 units of Bruno Fernandes but this was split across 2 purchases. Hence, the total purchase cost should be 658 (440 + 218). 

3) Erling Haaland (records #10-14) -  I have previously purchased and sold multiple units of Haaland. I purchased 50, another 50 and then another 100. I sold 200 and then purchased 15 and another 115 which takes me to my current portfolio holding of 130. Hence, the total purchase price should be 1,108 (987 + 121). The previous cost of purchases should be ignored. 

For completeness, I have a separate listing for the players and units currently in my portfolio. As I am unable to post two screenshots, this listing essentially details each individual player and the current portfolio quantity. i.e. the Player column de-duplicated with the Current Portfolio Q column.

Thank you so much in advance. I will of course give anyone credit for helping me. 

[RobertOdera]

Hi, @jamieeee 

Kindly post a sample file that we can work with.

Instead of the pic, can you share an excel version?

Otherwise, we have to type in your scenario input, which is tedious 🙂

[jamieeee]

Hi @RobertOdera 

Thank you for pointing that out, I will make sure I do this in the future. 

Table attached 🙂

[deviseetharaman]

Hi @jamieeee 

Is the selling information is available some where.

[jamieeee]

Hi @deviseetharaman 

I have uploaded 2 extra columns with the sell orders for these players. This effectively nets out to the 'Current Portfolio Q'. 

Let me know if you require anything else. 

[RobertOdera · Accepted solution]

Sure thing, @jamieeee 

Try this.

Please mark as a solution and like, if it works for you.

Cheers!

[jamieeee]

Thank you! @RobertOdera That has worked, really appreciate your help. 

Please can i ask:

1) What is the if formula stating. If the Record ID is the same as the row above? That doesn't make sense to me. 

2) Where can I learn about the 'coding' that goes into these conditional formulas? Is there a specific name for these formulas / code?

Thanks again.

Jamie

[deviseetharaman]

Updated WF based on excel

[RobertOdera]

Sure thing, @jamieeee 

In your initial ask, you stated:

1) Adama (records #1 and #2) - In my current portfolio, I hold 100 units of Adama. However, I previously owned 100 units of Adama (on 01/09/2019) but then sold those 100 units. I then decided to re-purchase 100 Adama units on 27/05/2020 which now sits in my portfolio. Hence, the purchase cost for my current portfolio should be 310 and NOT 94. The 94 should be ignored.  

2) Bruno Fernandes (records #5 and #6) - I currently hold 111 units of Bruno Fernandes but this was split across 2 purchases. Hence, the total purchase cost should be 658 (440 + 218). 

3) Erling Haaland (records #10-14) -  I have previously purchased and sold multiple units of Haaland. I purchased 50, another 50, and then another 100. I sold 200 and then purchased 15 and another 115 which takes me to my current portfolio holding of 130. Hence, the total purchase price should be 1,108 (987 + 121). The previous cost of purchases should be ignored.

The IF statement is working like so, derived from your notes:

IF [RecordID] IN (1,10,11,12)=-1

Alteryx runs an evaluation and returns -1 if the conditions are met, else 0 if not.

So if the record ID is either 1,10,11, or 12 it returns -1

THEN 1

If the return is -1 (i.e. the condition was met, then code as 1 (true)

ELSE 0

If the return is 0 (i.e. the condition was NOT met, then code as 0 (false)

ENDIF

You also asked where you can learn conditional arguments/ manipulation?

1. share common scenarios that you're running into and we will crowd-source approaches here for you to get a flavor

2. check out interactive lessons here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons

3. searches on the community

Cheers!

[jamieeee]

@RobertOdera 

Ah that seems so obvious now haha. Thank you very much!