How to find first date and the last date of the Mutual fund transaction and the order

[rajukrish21]

Dear all, 

I have a Mutual funds data set which contains the Customer ID, Product code, Transaction date( where he had done the Buy or SELL of the units), BUY/SELL(Bought or sold units on that date) and the Qty.

I wanted to find the first date when the customer is buying a product and its units and the last date where he sold all the units of that particular product. If he doesn't sell the product units fully, his product is still active and there will be no close date for that product

Customer ID	Product Code	Payments Date	Transaction date	Transaction Type	Quantity
A121	ABC		1/1/10	Buy	70
A121	ABC		5/6/10	Buy	80
A121	CDE		4/7/10	Buy	120
A121	ABC		6/8/10	Sell	20
A121	ABC		7/5/10	Buy	50
A121	CDE		6/8/11	Buy	100
A121	ABC		7/5/14	Sell	180
A121	ABC		1/1/23	Buy	100

a. For the dates, we use Transaction date column, if Transaction date column is NULL , we use value in Payment date column

b. If the quantity is NULL , we remove the data

c. We take only the transactions till 31/12/2023

d. Sequence column, we need to find the order in which the product is bought and sold. for eg , if a product A is bought and sold fully, the sequence will be 1. Again in future , if the customer is buying same product, the sequence will become 2.

Thanks In advance. Your help is much appreciated. :) 

Final Output should look like the below table:

Customer ID	Product Code	Transaction Open Date	Transaction Close Date	Net Units	Sequence
A121	ABC	1/1/10	7/5/14	0	1
A121	CDE	4/7/10	-	220	1
A121	ABC	1/1/23	-	100	2

[Yoshiro_Fujimori]

Hi @rajukrish21 ,

I followed the conditions you raised above, but 

the sample data does not include the test cases for the conditions a, b, c.

If you want to test, please add rows.

And I assume the date format of your sample data is d/m/yy.

I hope this helps.

Workflow

Condition a : Formula tool

Date = IF IsNull([Transaction date]) THEN [Payments Date] ELSE [Transaction date] ENDIF

Condition b : Filter tool

!IsNull([Quantity])

Condition c : Filter tool

[Date] <= "2023-12-31"