Calculated Fields

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:

• predefined functions and/or

• simple math calculations and other functions that are not predefined.

There are two types of calculated fields:

• pre-calculated (also called "calculated")

• post-calculated

Pre-Calculated Fields

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 and choose Calculated Field:

The New Calculated Field screen will open:

Here you will need to:

1. Assign a name to your new pre-calculated field.

2. 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

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:

1. Create a visualization (or a Pivot table) by adding fields from your data source in the Data editor.

2. Click/tap the F(x) button next to Values to open the New Calculated Field screen:

3. 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.

Using the Predefined Reveal Functions

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, semester, fsemester, fyear, hour, millisecond, minute, month, monthname, monthshortname, applytimezone, currenttimezone, datetimefromunixts, now, quarter, second, time, today, weekday, weeknum, year.

• Information: empty, isempty.

• Logic: and, false, if, not, or, true.

• Lookup & Reference: previous, row.

• Math: abs, exp, log, log10, mod, rand, randbetween, sign, sqrt, trunc.

• String: concatenate, find, len, lower, mid, replace, sortinterval, trim, upper.

note

Limitations to IF Conditions. IF conditions have known limitations when included in aggregation functions in pre-calculated fields. The need to go through every record that many times, trying to check an IF condition within an aggregation formula causes underperformance issues, hence it's not supported.

Creating Calculated Fields without using the predefined functions

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.

What to consider when using the Reveal 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.