Payment history only after a certain date?

I have a file with a payment history stretching several years for a number of customers. Separately, I have a column with a their contract termination date. I would like to map a payment history, whereby only payments after termination date appear, and the ones before are displayed as zero.

I am new to Alteryx, so the only idea that came to my mind involved Formula tool, and a lot of iterative actions, i.e. create new column for every month, and insert simple expression relating to the column month and termination date.

Is there a quicker way?

Preparation

Accepted answers

JohnJPS

OK, I see. For this, you can use the "Multifield Formula" tool, which has the ability to reference the column name...

Set it to "Numeric" field types, and select all the date-looking column names, (click "All" button, then un-select "Client")... and then use the formula:

IIF([_CurrentFieldName_] > [Termination date],[_CurrentField_],0)

( PS, and to replace the existing data, unselect the "Copy Output Fields and Add"... checkbox.)

All comments

JohnJPS

Hi @bump,

If you could mock up a sample data file, that would help. Given columns for payment date and contract termination date, it's as easy as:

New field: "CalculatedDate" of type Date, using this formula:

IIF([PaymentDate] > [ContractTermDate],[PaymentDate],Null())

... so I'm guessing you don't have those columns without doing some initial work to get them... but not knowing your data, I can't really comment.

bump

Hi John,

Many thanks for reply. I have attached sample data.

The end goal is to have payment history that would only take into account payments occurring after termination date.

After inputting data into Alteryx, my only idea so far is to use Formula tool and create a separate output field for every month, and appropriate expression (similar to one you have above). That would, however, require many iterative actions, as in some cases I might have as many as >60 months of payment history. I was wondering whether there;s a neat trick that might somewhat automate/shorten the process.

sample_payment.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703