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.
For the orders in the following set, you want to charge interest for those ones that are older than 90 days.
The first step is to create a column containing today's (3/16/16) date value:
derive type:single value:TODAY() as:'Today'
You can now use this value as the basis for computing the number of elapsed days for each invoice:
derive type:single value:DATEDIF(OrderDate, Today, day)
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:
derive type:single value:IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') as:'TakeAction'
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:
derive type:single value:IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) as: 'TakeAction'
By using nested instances of the
IF function, you can generate multiple results in the
For the items that are over 90 days old, you want to charge 5% interest. You can do the following:
set col:Amount value:IF(TakeAction == 'Charge interest',Amount * 1.05,Amount)
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