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 |
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
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
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.
@SArielle
It will be better for you to share the complete sample input data (different clients would have different contract start dates) 😁
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 |
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.