EXAMPLE - DATEDIF Function
This example illustrates how to calculate the number of days that have elapsed between the order date and today.
Function:
Item | Description |
---|---|
DATEDIF Function | Calculates the difference between two valid date values for the specified units of measure. |
TODAY Function | Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter. |
IF Function | The |
Source:
For the orders in the following set, you want to charge interest for those ones that are older than 90 days.
OrderId | OrderDate | Amount |
---|---|---|
1001 | 1/31/16 | 1000 |
1002 | 11/15/15 | 1000 |
1003 | 12/18/15 | 1000 |
1004 | 1/15/16 | 1000 |
Transformation:
The first step is to create a column containing today's (03/03/16) date value:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | TODAY() |
Parameter: New column name | 'Today' |
You can now use this value as the basis for computing the number of elapsed days for each invoice:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | 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:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') |
Parameter: New column name | '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:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) |
Parameter: New column name | 'TakeAction' |
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:
Transformation Name | |
---|---|
Parameter: Columns | Amount |
Parameter: Formula | 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 Amount
column.
Results:
OrderId | OrderDate | Amount | Today | TakeAction |
---|---|---|---|---|
1001 | 1/31/16 | 1000 | 03/03/16 | no action |
1002 | 11/15/15 | 1050 | 03/03/16 | Charge interest |
1003 | 12/18/15 | 1000 | 03/03/16 | Send letter |
1004 | 1/15/16 | 1000 | 03/03/16 | Send letter |