Landing Pages - Calculated Field Options

The Datafile database structures allow various calculated field option that can be included within a database – adding, subtracting, multiplying, and dividing other data item values. This requires restructuring the database and defining a formula where the field may only be required on a landing page – either for display or for use in filters.

This new feature allows you to create calculation items on the fly for inclusion in a landing page tab and filters but also greatly extends the functionality of the calculations available over the database structure options.

Calculated Fields

To view the calculated fields available for a tab, click on the landing page tab and choose the ‘Show Properties’ option. If using Data Visualisations with multiple tabs, then calculated fields are created for the tab you are on only (though they are copied if you ‘clone’ a tab).The Calculated Fields option has been added to the right-hand panel with the Saved Filters and Conditional Highlight options.

Click the ‘+’ icon to add a new calculated field.

Highlight the item and click the ‘pencil’ icon to edit a calculated field.

Highlight the item and click the ‘-‘ icon to remove a calculated field.

Adding A Calculated Field

When adding a calculated field, you are prompted for four options.

Caption – the title of the calculated field to be displayed as the column heading.

Expression – the formula for the calculation. If you are familiar with calculation builders in spreadsheets these options will be familiar to you click the ‘…’ icon to enter the expression editor.

The expression editor is split into four panels – the top panel shows the formula entered so far, the bottom left panel shows the option types, the middle panel shows the fields/values available for the option type and the right panel shows help text for the selection.

The example above is creating an average demand calculation – adding up all the demand fields and then dividing the result by ‘12’.

Calculation options are like those within spreadsheet programs such as Microsoft Excel. You can build the formula required by entering required text in the editor, dropping in field names by selecting the ‘Fields’ option type and double-clicking the field name from the available options within the middle panel. Standard mathematical formula options apply ‘+’, ‘-’, ‘*’ and ‘/’ for plus, subtract, multiply and divide. Use () to group calculations that you wish to be performed first and so on.

You can also use mathematical functions – on the formula above we’re setting a text value based on a formula of whether free-stock is below minimum stock using the IF command – setting the value to ‘Yes’ if minimum stock is less than 0, otherwise ‘No’.

If you have an error in the formula the system will display the text ‘Syntax Error’ to enable you to revise the calculation before saving it. Click the OK button to save the calculation and return to the edit window.

Type – select the type the formula is to be displayed as.

Whilst the normal value would be a ‘float’ you can output the value as a text string, a currency value, an integer, a Date/Time value, or a Boolean Y/N value depending on the calculation you have entered.

Decimals – if the calculation is a number (float) set the resulting no of decimal places to calculate the value to.

Once you have added a calculated field the item will be automatically added as a field into the current landing page tab. You can also use these calculated items within other calculated fields (they will appear in the data items list)

Decimals – if the calculation is a number (float) set the resulting no of decimal places to calculate the value to.

Once you have added a calculated field the item will be automatically added as a field into the current landing page tab. You can also use these calculated items within other calculated fields (they will appear in the data items list)

Using Calculations in Filters

A calculated field created for the landing page can also be used in filters, the item added appearing in the list of fields in the filter builder.

However, you can also use the calculations option as a comparison item allowing you to compare one data item to another or to compare a data item against a calculation specific for the filter.