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.

OrderId | OrderDate | Amount |
---|---|---|

1001 | 1/31/16 | 1000 |

1002 | 11/15/15 | 1000 |

1003 | 12/18/15 | 1000 |

1004 | 1/15/16 | 1000 |

**Transform:**

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 `TakeAction`

column.

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 `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 |