Several different types of calculations can be added to a visual analysis, including math, date, text, logic, and conversion calculations. Prior to adding a calculation, a new column must be added to the data fields in the visual analysis and assigned a name to identify the type of calculation being added.
A calculation is created to determine the original term of each contract to identify whether long-term or short-term contracts are being written. Prior to adding the calculation, a visual analysis must be built to ensure the needed fields are included (Effective Date and Original Exp Date), as well as fields that provide supporting data for the calculation, such as contract number, value, and type. For simplicity, the visual analysis built in Creating a BI Visual Analysis will be used.
1. Click Reports in the Navigation Toolbar, and select the BI Toolbox link in the menu.
3. In the Discovery screen, scroll to the area below the chart or table.
4. Click , located below the button, to display a Table Menu, and select + Add New Column.
5. (Optional) When the Calculation window displays, re-name the calculation by clicking in the Calculation field and typing over the existing text.
6. Build a formula that calculates the term of a contract using the effective date and original expiration date:
- Click the Date tab to reveal the date functions, and click DATEDIFF to add this function to the formula window.
- Hover over the function to display the correct syntax for the function and a brief description.
- Enter 'Month' to the formula to calculate the time in months.
- Enter 'Day' or 'Year' to calculate the term in days or years.
- Add a comma after the text encased in single parenthesis.
A comma MUST be appended to each component of the formula, except the last component, which must be appended by a closing parenthesis. User-provided components, such as 'Month' appear in red text.
- Click the Columns tab to reveal the columns available for the DATEDIFF function.
- Click Effective Date to add the field to the formula.
- Add a comma after the field.
- Click Original Exp Date to add the field to the formula.
- Add a comma after the field, and then add a closing parenthesis.
- Click TEST to validate the calculation: DATEDIFF('Month',[Effective Date],[Original Exp Date])
- Click Add to add the calculation to the new column.
- Click to close the Calculation window.
7. Click Save.
8. When the prompt displays:
- Click Save to update the visual analysis.
- Create a copy of the visual analysis with the calculated field:
- Update the name of the visual analysis in the Name field.
- Click Save as New.
9. (Optional) Click the icon in the header of the calculated field to display a prompt that allows you to edit the calculation, apply formatting, group the calculated results, replace the values with graphics, and apply color.