Calculations can be between any Currency or Number fields available in the data source, even if they are not displayed. Once a calculated column is configured, it can be used in the calculations for other columns, or in visualizations.
For example, an interactive report built over the GLPeriodBalances table has currency fields for Credit and Debit. If you wanted a column that shows the Net Amount, you could create a calculated column which subtracts the Debit field from the Credit field. With these configurations, you could have a report data table which shows the following columns:
| Company (field on GLPeriodBalance table) | Credit (field on GLPeriodBalance table) | Debit (field on GLPeriodBalance table) | Net Amount (calculated column of Credit - Debit) |
|---|
Or, you could configure a calculated column which divides Net Amount by the aggregated sum of the Net Amount column. With these configurations, you could have a report data table which shows the following columns:
| Company (field on GLPeriodBalance table) | Net Amount (field on GLPeriodBalance table) | Percent of total Net Amount (calculated column of Net Amount Total / Net Amount Value for each row) |
|---|
Create a calculated column
To configure a calculated column, select the Create
button in the Calculated Columns section. Name your column, then drag fields, other calculated columns, and math operations into the expression.
Optionally, select a value in the Type field. If no value is selected, the system automatically selects the most appropriate format type for the column. Options include:
- Number—Formats the column to display as a number. This allows you to select the number of decimals you want displayed for the value, or to format the column values as percentages.
- Currency—Formats the column to display with whatever currency value is associated with the data item.
Once the calculated column is created, you can select it like any other field when you build your summary table in the Table Layouts section.