There are three types of special Calculation Columns in Apricot's Report Builder: Number Calc, Date/Time Calc, and Text Calc. Multiple calculation columns can be added to a section, and the results from one calculation column can be used in subsequent calculation columns.
Date/Time Calc columns can display the difference between two dates or times or determine what date is a certain number of days before or after a particular date. Follow the steps listed below to learn how to calculate dates or times within a report column.
Note: Date/Time Calc columns truncate values. For example, if you're running a calculation to see how many years are between the dates 8/31/2022 and 8/30/2024, the calculation column will display a "1" as the second year has not technically occurred yet.
Step 1: Add a Date/Time Calc column
In the upper right-hand corner of the Report Builder, click on Special Columns in the Field Choices palette then expand Calculation Columns. Drag and drop the Date/Time Calc column into your report section.
Step 2: Create your calculation
Note: Date/Time Calculations truncate values. For example, if you're running a calculation to see how many years are between the dates 8/31/2021 and 8/30/2023, the calculation column will display a 1 as the second year has not technically occurred yet.
Open the column properties by clicking the gear icon next to the calculation column's name.
You can create a column name in the Name field based on what it's meant to calculate. In this example, we are calculating what date is 90 days after each participant's program enrollment date.
Each calculation column can only have one Calculation Type, and each Calculation Type requires different field types:
Days Between: Requires two columns with date data
Time Between: Requires two columns with time data
Days After: Requires one column with date data and a static value
Days Before: Requires one column with date data and a static value
In the Calculation Columns section, use the dropdown fields to select which values you want to calculate. If you selected a calculation type that involves days, all columns with date values will be available here. If you selected a calculation type that involves time, all columns with time values will be listed. Choosing [Static Value] allows you to type in your own numeric value or calendar date to use in the calculation. This will remain a constant value when you run your report.
Step 3: Apply your changes
Once the column's properties have been set, select Apply to save your changes.
Finally, save your report from the Report Actions palette. You can also publish the report to make the changes immediately available to other users running the report.
What else do you need help with?
Not what you're looking for? Navigate to overview