Plugging in the revenue year based on transaction dates

Question

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

202220232024Client 150120500

alexnajm · Answer

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.

Plugging in the revenue year based on transaction dates.yxmd

SArielle · Answer

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 :

ClientTransaction DateAmountsClient 115 Apr 202450Client 21 Jan 202450Client 41 Jan 202450Client 315 Feb 2023100Client 215 Feb 2022100Client 415 Feb 2024100Client 331 Oct 2022100Client 230 June 2023100Client 130 Mar 2023100

Expected Outcome

Client2021202220232024Client 1 100 50Client 2100 150 Client 3 200  Client 4   150

Qiu · Answer

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