Grouping & Formula Options

Note: Formulas and variables can be managed from several places, principally the Data > Formulas menu. Please see Formula Fields for a general guide to using formulas and variables in Omniscope.   

Table View Grouping options - formula calculation precedence

Grouping options and formula calculation options can interact. In the Table View's Formula menu, two additional options are provided allowing you to configure the precedence of formula calculation when you are Grouping by fields or using Group > Show overall summary row. These precedence settings determine whether fields with formulae defined are calculated at individual record level, or at Grouped level:

Table View: View Toolbar: Formulas > Calculate group result values over formula field results

This is the default behaviour (grouping/summing of row values happens after formulae are calculated). Formulas are calculated for ungrouped data, then the function chosen (e.g. sum) is applied to the formula results, producing the grouped cell value.

Table View: View Toolbar: Formulas > Calculate formula field results for group result values

This option changes the precedence of the calculations, such that a formula such as =[USD Volume] / [Deal Count] in an grouped table using grouping function Sum, is evaluated as Sum(USD Volume) / Sum(Deal Count) for the grouped rows. Notice that with this option set, there are two "sum" concepts at play: inside a formula, SUM (Column A, Column B, Column C...) calculates the SUM of multiple fields within the same row. In the grouped table, the grouping function Sum calculates the vertical sum of all record values within the same field (column) in the grouped row.

Note: some of the now-deprecated Visokio DATASET functions will not work with grouped table rows in this mode. If you use this option, and you have grouped table rows while using certain DATASET functions, you will see "error" in the grouped cells of any formulae using the unsupported functions. For more information on substituting SUBSET functions for the now-deprecated DATASET functions, see the Functions Guide in our KnowledgeBase.