# 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

TheIFfunction allows you to build if/then/else conditional logic within your transforms.

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 New formula Single row formula TODAY() 'Today'

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

 Transformation Name New formula Single row 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 New formula Single row formula IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') '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 New formula Single row formula IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) '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 Edit column with formula Amount 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