A formula is added to an analysis by inserting a calculated column into the Table section, and then building the formula using program fields, date and value functions, and math operators. Help in creating the formula is available in the application, which includes definitions and descriptions of functions and math operators.
A formula is created to determine the difference between the proposed value of a contract and its actual value, as recorded in the Contract Profile screen. Prior to adding the calculation, an analysis is needed that includes the Contract data set with both the Proposed Value and Contract Value fields selected, as well as fields that provide supporting data for the formula, such as contract number, status, type, agreement type, effective and expiration dates, and company name. A filter should also be applied to both the Proposed Value and Contract Value fields so that the value in these fields is greater than 0.
1. Click Reports in the Navigation Toolbar, and select the BI Toolbox link in the menu.
3. In the Analysis screen, click Formula in the toolbar at the top of the screen.
For help in creating a formula, click to display a Self-Service Analytics file in a new browser tab.
4. When the Add a new column from a formula section displays, provide a name for the formula and build it:
- In the Name field, enter a name for the column that will be appended to the Table and used to display the calculation.
- Using the fields in the Insert a column list and math operators (+ - * /), build the formula in the Formula list box:
- Select Contract Value in the list and click to display [contract.Contract Value] in the list box.
- Add the - (minus) operator, making sure there is a space before and after the operator.
- Select Proposed Value in the list and click to display [contract.Proposed Value] in the list box.
- In the Data Type list, select Number since we are calculating the difference between two values.
- In the Display Format list, select $#,###.00 (currency format) since we want the difference displayed in dollars and cents.
- Click .
- Remove a formula by clicking its button.
- Replace a formula by updating any of the fields and/or correcting the formula and clicking the button.
5. Review the results of the calculation for each record in the new column, which appears as the last column in the Table section of the screen.
6. (Optional) Click Formula in the toolbar to hide the formula section.