Payment history only after a certain date?

[bump]

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?

[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.

[JohnJPS · Accepted solution]

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.)

[RodL]

If you want to make something like this truly dynamic, then the "best practice" when you are dealing with unknown number of columns is to "verticalize" the data.

I've attached a sample workflow that shows how to do this, but the concept is to get the data into a column of description/value pairs. Once you do that, you can apply filters and formulas in a singular fashion and not be concerned about rewriting when new columns are added.

In your case, once you have your months filtered out, you can repivot if necessary with a Cross Tab to get it back into a tabular format (although typically I've seen where the user is really just wanting the information in summary form, or similar, and you don't need to do that.