Reveal allows you to define new fields in the data set, named calculated fields. These fields are created by using expressions (formulas). An expression can be a combination of existing field(s), constant values and:
There are two types of calculated fields:
pre-calculated (also called "calculated"), and
Pre-calculated fields are evaluated before executing data editor aggregations. This means in order to apply a certain formula, Reveal will go through every record in your field’s dataset once or several times. Because of this, pre-calculation is likely to be underperforming in terms of speed when working with large datasets.
To add a new pre-calculated field, click/tap the + button in the Fields panel:
The New Calculated Field screen will open:
Here you will need to:
assign a name to your new pre-calculated field;
enter a formula (expression). In the fields section you will find a list of all the existing fields to choose from. You can use one or more fields to create the formula by clicking on the selected field or typing its name in square brackets. Choose one of the predefined functions listed in the Functions section or use a simple math calculation (as shown above).
The new pre-calculated field will show up at the bottom of your Fields list:
In the example above, the new calculated field is used with a grid visualization where no aggregation is being applied on the data fields. Pre-calculated fields can also be used with Pivot grids. In this case, aggregation (e.g. summarization) will be applied to the already calculated records in the pre-calculated field.
Post-calculated fields can be created when working with Pivot tables and other visualizations, which execute an aggregation on the data fields in the Data editor. Post-calculated fields are always built by applying a formula on already summarized values.
To create a post calculated field you will need to:
Create a visualization (or a Pivot table) by adding fields from your data source in the Data editor.
Click/tap the F(x) button next to Values to open the New Calculated Field screen:
Give a name to your new calculated field and apply a formula to the summarized value(s). Pay attention that the list of Values includes the data fields you used in your visualization after they have been aggregated (Sum of Spend, Sum of Budget, not: Spend, Budget).
If you need to use other fields from your data source that are not included in the Data editor, you can add them by clicking/tapping on the + button next to Values. Since post-calculated fields are created only by using aggregated values, you will first need to select an aggregation from the dropdown list to be executed on the data field.
You can also skip step 1, create your post-calculated fields first or use only post-calculated fields in your visualization.
Post-calculation tends to perform better than pre-calculation when working with large datasets
For both pre-calculated and post-calculated fields, you can use one of the available functions within Reveal:
Aggregation: AVERAGE, AVERAGEIF, COUNT, COUNTIF, MAX, MAXIF, MIN, MINIF.
Date: DATE, DATEVALUE, DAY, FORMATDATE, FQUARTER, FYEAR, HOUR, MILLISECOND, MINUTE, MONTH, MONTHNAME, MONTHSHORTNAME, NOW, QUARTER, SECOND, TIME, TODAY, WEEKDAY, WEEKNUM, YEAR.
Math: ABS, EXP, LOG, LOG10, MOD, RAND, RANDBETWEEN, SIGN, SQRT, TRUNC.
String: CONCATENATE, FIND, LEN, LOWER, MID, REPLACE, SORTINTERVAL, TRIM, UPPER.
You can also create Calculated Fields without using any of the predefined functions; for instance, for simple math calculations like subtractions, divisions, additions or multiplications. In this table, you will find some examples that do not use the predefined functions.
Text strings should be included between quotation marks. Examples include locale ("en") and date formats ("dd/mm/yyyy").
Fields included in your formula should be included between square brackets. Examples include [Wage], [BirthDate] and [EmployeeID] for the HR Dataset.