When to Use Formulas vs Core Blocks in Omniscope

When working with data in Omniscope, you may face a choice: should you solve a challenge using a formula or a core block? The answer depends on context, scale, and the type of operation required.

1. Block-Level Calculations

Formulas can be used to perform calculations per row or across subsets of records. For example, to calculate the total sales value for each product, with grouping on a single field [Product], or to list all tickers for each [Category] and [Composite Rating] combination (grouping on multiple fields). 

Let's have a look at this particular syntax:

SUBSET_UNIQUESLIST([Ticker], 
  INTERSECTION(
    subset([Category]), 
    subset([Composite Rating])
  )
)

The formula creates a list of unique Ticker values for each combination of values in the other two fields.

In such cases, using a core block like Aggregate tends to be more efficient. The same result can be achieved by grouping on [Category] and [Composite Rating], and applying Unique values list to [Ticker] as a measure for the text field. This can then be combined with Unique values count to order the results by the number of tickers in each aggregated group.

2. Situations Where Formulas Provide Precision

Core blocks can often handle the task, but formulas can combine multiple functions, apply logic, and may be the better option when you need a precise, single-field result. 

Example: extracting the author’s handle from a (tweet) [Text] such as:

RT @newsupdate Big thanks to @citycouncil for supporting the new community project!

A Split values block could separate all words using a space separator, but it produces multiple new fields that are not required for further analysis.

A formula extracts the required value with precision and minimal overhead. It returns the first string that has "@" in front and a space after (note that in this text there are two values that satisfy this criterion).

FINDBETWEEN([Text], "@", " ")

Practical Guidance

• Use formulas when you need precision, flexibility, or single-field transformations.

• Use core blocks when working with large datasets, aggregations, or operations where performance is critical and the same calculation is applied to a large number of records.

• Formulas can be applied in the workflow area, in a Field Organiser block, or inside the Report block. The Interactive Functions picker lists all functions with syntax and indicates where each one can be used.