Calculated Fields and Measures allow you to calculate additional information from your captured data.
Date calculations allow you to create a new field based on date records in the database.
This could be used to determine the date a review is due for a client.
Instructions
Step 1. Open or create a new Ad Hoc View (refer to Creating Ad Hoc Views for further information)
Step 2. Click on the Icon in the Field pane.
‘Create Calculated Field…’ is displayed.
Step 3. Click Create Calculated Field…
Step 4. Enter a Field Name
(e.g. Review Date)
Step 5. Enter the formula details required
To create a calculated field that shows the date, 3 months after another date field:
DateAddSQL(<Date Field>, 3, ‘MONTH’)
In the example below, the date field = Member Since on the Client record
The Review Date is the calculated field using formula
= DateAddSQL(“Member Since (Clients)”, 3, ‘MONTH’)
To add a number of days to the date:
DateAddSQL(<Date Field>, 7, ‘DAY’)
To add a number of years to the date:
DateAddSQL(<Date Field>, 5, ‘YEAR’)
Remember to select the correct date field to calculate from.
You can use a similar formula to determine an earlier date based on a date stored in the database.
You may use this to calculate a date a month, year or number of days before a recorded date.
To create a calculated field that shows the date, 3 months before another date field:
DateSubSQL(<Date Field>, 3, ‘MONTH’)
In the example below, the date field = Member Since on the Client record
The Review Date is the calculated field using formula
= DateSubSQL(“Member Since (Clients)”, 3, ‘MONTH’)
To subtract a number of days from the date:
DateSubSQL(<Date Field>, 7, ‘DAY’)
To subtract a number of years from the date:
DateSubSQL(<Date Field>, 5, ‘YEAR’)
Remember to select the correct date field to calculate from.
Further Information
For further information on Ad Hoc Views, refer to the following articles: