Calculations on date fields

In a data collection, you can make calculations on date fields. For example, you can calculate the number of days between a certain date and today's date. You can use the calculated field in reports, analyses, views, etc.

To add a calculated field:

1. Open the data collection.
2. Add the date field, to which you want to add a calculation, to the data collection.
3. Select the date field.
4. Click on: Calculation.

   

   Enter the data for the calculation, for example to determine the age in days, as in the above example.

5. Click on: OK.
6. Click on: Next.
7. Click on: Next.
8. Click on: Example.
9. Check the calculated value.

   

To specify the reference date:

You use the reference date to specify the number of days over which an event occurs, or how many days ago it occurred. You can use any date field from the data collection as a reference date. First, determine the date field from the data collection that you want to use for the reference date.

Example:

You want to receive an alert when the repayment date for an instalment of a loan for an employee is approaching. The date field you want to use as a reference date is Date repayment from the Employee / loan / repayment line table.

Shifted reference date:

Sometimes you do not want to use the date from the data collection, but rather a date that is a few days before or after it. This is a shifted reference date.

Example:

You want to receive an alert, 5 days before an invoice becomes due, that the due date is approaching. In this case, you use the Expiry date field with minus 5 days in the calculation.

Recurring date

Some reference dates recur annually. For instance, a birthday returns every year. This is a recurring date. The recurring date is equal to the date field from the data collection, where the year value is incremented each time.

1. Select the Recurring date check box in the properties of the calculation.
2. Select the Number check box if you want to use a shifted reference date.
3. Select the correct value in Show as.

   

   In this example you see three reference dates. The middle reference date is equal to the system date. If you have selected the Recurring date check box, you see the date in this year that is closest to the reference date. In Calculated date, you see the effect of a shift on the reference date. In Result, you see the effect of your selection in the Show as field.

   Example:

   For a birthday alert, you need three calculations based on the Date of birth field:

• A field that calculates the date of the birthday this year. Call this field 'Birthday this year', for example. For this field, select the Recurring date check box and display the field as Date.
• A field that calculates the number of days after which the birthday will take place this year. Call this field 'Difference in days', for example. For this field, select the Recurring date check box and display the field as Difference with today in days (text).
• A field that calculates the age. Call this field 'Age this year', for example. Select the Recurring date check box, and display the field as Difference in years with reference date.