Date: Fri, 21 Jan 2022 10:25:17 +0000 (GMT) Message-ID: <1557924333.106534.1642760717739@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_106533_56800235.1642760717739" ------=_Part_106533_56800235.1642760717739 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - DATEDIF Function

EXAMPLE - DATEDIF Function

This example illustrates how to calculate the number of days that h= ave elapsed between the order date and today.

Function:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
DATEDIF Function Calculates the difference between two valid dat= e 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 z= one by optional parameter.
IF Function The IF function= 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 tho= se ones that are older than 90 days.

=20 =20 =20 =20
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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula TODAY() 'Today'
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20
Transformation Name <= code>New formula Single row formula DATEDIF(OrderDate, Today, day)
=20

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 jus= t calculating the number of days, you could write out the action to underta= ke. Replace the above with the following:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula IF((DATEDIF(OrderDate, Today, day) > 90= ),'Charge interest','no action') 'TakeAction'
=20

To be fair to your customers, you might want to issue a notice at 45 day= s that the invoice is outstanding. You can replace the above with the follo= wing:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula IF(DATEDIF(OrderDate, Today, day) > 90,= 'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter',= 'no action')) 'TakeAction'
=20

By using nested instances of the IF function, you can gener= ate 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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula Amount IF(TakeAction =3D=3D 'Charge interest',Amo= unt * 1.05,Amount)
=20

The above sets the value in the Amount column based on the = conditional of whether the TakeAction column value is Ch= arge interest. If so, apply 5% interest to the value in the Am= ount 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

=20
=20