## Key

• This line was added.
• This line was removed.
• Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

This example illustrates how to use the `DATEDIF` function to calculate the number of days that have elapsed between the order date and today for purposes of informing the customer.

Source:

For the orders in the following set, you want to charge interest for those ones that are older than 90 days.

OrderIdOrderDateAmount
10011/31/161000
100211/15/151000
100312/18/151000
10041/15/161000

Transformation:

The first step is to create a column containing today's (03/03/16) date value:

D trans
RawWrangle true 'Today' step derive type:single value:TODAY() as:'Today' Formula type Single row formula Formula TODAY() New column name New formula

You can now use this value as the basis for computing the number of elapsed days for each invoice:

D trans
RawWrangle true step derive type:single value:DATEDIF(OrderDate, Today, day) Formula type Single row formula Formula DATEDIF(OrderDate, Today, day) New formula

The age of each invoice in days is displayed in the new column. Now, you want to add a little bit of information to this comparison. Instead of just calculating the number of days, you could write out the action to undertake. Replace the above with the following:

D trans
RawWrangle true 'TakeAction' step derive type:single value:IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') as:'TakeAction' Formula type Single row formula Formula IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') New column name New formula

To be fair to your customers, you might want to issue a notice at 45 days that the invoice is outstanding. You can replace the above with the following:

D trans
RawWrangle true 'TakeAction' step derive type:single value:IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) as: 'TakeAction' Formula type Single row formula Formula IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) New column name New formula

By using nested instances of the `IF` function, you can generate multiple results in the `TakeAction` column.

For the items that are over 90 days old, you want to charge 5% interest. You can do the following:

D trans
RawWrangle true step set col:Amount value:IF(TakeAction == 'Charge interest',Amount * 1.05,Amount) Columns Amount Formula IF(TakeAction == 'Charge interest',Amount * 1.05,Amount) Edit column with formula

The above sets the value in the `Amount` column based on the conditional of whether the `TakeAction` column value is `Charge interest`. If so, apply 5% interest to the value in the `Amount` column.

Results:

OrderIdOrderDateAmountTodayTakeAction
10011/31/16100003/03/16no action
100211/15/15105003/03/16Charge interest
100312/18/15100003/03/16Send letter
10041/15/16100003/03/16Send letter