Plugging in the revenue year based on transaction dates

[SArielle]

Hi All, 

I was wondering if anyone have any idea how to create a workflow for the following. 

Contract Start Date :  1 Apr 2022 to 31 Mar 2025

Transaction Date #1 :   15 Jan 2023, $50

Transaction Date #2 :   7 June 2023, $120

Transaction Date #3 :  1 Apr 2024, $500

Expected Outcome

	2022	2023	2024
Client 1	50	120	500

[BRRLL99]

Initial Target should be to extract Amount and Year

Step 1:

Add Formula tool and create 3 new columns

1 -    year : REGEX_Replace([Field1], '.*(\d{4}).*', '$1')

2 -     Amount - replace(REGEX_Replace([Field1], '.*(\$\d+).*', '$1'),'$','')

3 -   Client - 'Client 1'

Step : 2

Using Select tool, and remove Field1 column

Step 3 :

Use Cross Tab

Group By : Client column

Change column headers : Year

Values for new columns : Amount

Method : First

[Yoshiro_Fujimori]

Hi @SArielle ,

It may not be sophisticated, but if you want to get the fiscal year of each date, you may create a table of Date and Year, and then join it with the transactions.

Input

Contract Peiod

Year	Start	End
2022	2022-04-01	2023-03-31
2023	2023-04-01	2024-03-31
2024	2024-04-01	2025-03-31

Transactions

Client	Date	Amount
Client 1	2023-01-15	50
Client 1	2023-06-07	120
Client 1	2024-04-01	500

Output

Client	2022	2023	2024
Client 1	50	120	500

Workflow

[SArielle]

This is a great idea for single client but as different clients would have different contract start dates, creating a table with the year on the side might get very tedious. 

[Qiu]

@SArielle 
It will be better for you to share the complete sample input data (different clients would have different contract start dates) 😁

[SArielle]

Sure let me add on more samples just to illustrate my challenge. 

Contract Dates :

Client 1 : 1 Apr 2022 to 31 Mar 2025

Client 2 : 30 Nov 2021 to 31 Oct 2024

Client 3 : 1 May 2021 to 30 Apr 2023

Client 4 : 1 Jan 2024 to 31 Dec 2024

Transactions :

Client	Transaction Date	Amounts
Client 1	15 Apr 2024	50
Client 2	1 Jan 2024	50
Client 4	1 Jan 2024	50
Client 3	15 Feb 2023	100
Client 2	15 Feb 2022	100
Client 4	15 Feb 2024	100
Client 3	31 Oct 2022	100
Client 2	30 June 2023	100
Client 1	30 Mar 2023	100

Expected Outcome

Client	2021	2022	2023	2024
Client 1		100		50
Client 2	100		150
Client 3		200
Client 4				150

[alexnajm]

Workflow attached. There is no 2021 data in the above transactions, so this workflow does not have that column - if 2021 data is introduced, then it would automatically be included!

If you need to consider the contract dates in filtering out transactions, do so before the Crosstab tool! However, if the transaction occurs in reality, I would assume you'd want it included.