Using formulas, calculations and variables in Omniscope

Calculations can be performed in several areas of the Omniscope application:

• in the workflow, using the Field Organiser block
• on the individual report level, using the Report data sources functionality
• in the Content and KPI view, in the Report area
• on a chart level, allowing the use of multiple Formula measures

Field Organiser block

From the ‘best practice’ point of view, adding calculated fields in the preparation phase, in your ETL, makes the calculation part of the data transformation routine, enabling all new records to undergo the same treatment. The data is now available to be visualised in one or multiple reports, stemming out of the transformation process. It is centrally managed and easy to access or modify if needed. 

Inside the Field organiser block below (screenshot below) the new formula fields, just like any modifications, are highlighted yellow, so anyone checking/editing the formulas won't have any trouble finding them. 

Note: formula results are imported as static values, so will not be re-calculated based on the user interaction in the dashboard. If this is the desired behaviour - the next paragraph will be useful.

Report data sources

In the Evo application, it is possible to create multiple dashboards originating from the same workflow, allowing the user to manage a single ETL process while publishing different reports. These reports might contain completely unrelated datasets, therefore the analyst may have to perform calculations on the target report level. Other reasons to move calculations to the Report data sources (available from the 3-dot menu inside each report) include:

• ‘Live query’ data input mode - when Omniscope is connected to a large database and is ‘streaming the data’, rather loading it, therefore data 'skips' the ETL phase.
  
  Note: You would need to ensure that the source (i.e. database) is connected directly to the Report block, and doesn't contain any intermediary blocks. You should instead use the Report data sources to perform the Aggregation, Joins and Formula fields.
  
  
• Variables – an incredibly useful tool to allow the end-user to control the dashboard by interacting with numerical/date/text variables, which is built into the formula.
  
  Use cases: ‘what if’ scenario, where the user wants to recalculate the charts instantly by interacting with the variables such as exchange rate/ market price, and watch how different independent variable values affect the outcome (see demo). Another use case is a situation where the dataset contains multiple category fields that the user may wish to use for comparison, which would result in multiple dashboard tabs; alternatively, multiple KPIs that might be good candidates for the chart measures. To avoid multiplication of identical charts which differ only in the choice of the split or measure field, variables could be used to give control to the end-user, so they can pick the comparison fields on a single tab, while still having the access to all the combinations e.g. give the option to the viewer to compare the sales by [Country] or [Company] or [Product] and use for the numeric measure [Value $] or [Units] or [Weight].
  
  All this could be performed on a single page - see demo.
  
  
• Dynamic calculations are driven by the end-user. If a Query block is added in the Report data sources area, before the Aggregation or Formula blocks (see the images below), the consumer of the report will be able to control the outcome of the calculations, by allowing all or only some of the data to pass through to the calculation step.

• Performance optimisation – when dealing with large datasets (regardless of the origin).  Report data sources formula builder interface contains a smaller number of functions, compared to the Field Organiser - these are optimised for big data use and complemented by the functions in the Aggregation block. Instead of performing simple Subset functions on the relevant data segments - consider using an Aggregate block.

Report Data Sources universe offers powerful advanced functionality by combining the Formula, Aggregation, Query and Join blocks to create a central calculation/ querying repository for multiple visualisations. 

It could be used to create multiple new data sources and multiple new calculated fields, in each data source. Unlike the fields created in the ETL phase, these new sources/fields are only accessible inside the specific report. 

Important: If you wish your formula results to respond to the filtering/selection (to control the data flow going to the calculation step) you need to open Report Data Sources from the 3-dot menu (report open in the edit/design mode):

1. Create a 'derived data source' from one of the original Report block data inputs. 

(see image: Source 3 is a Report input named "Weather data", while Source 4 was created from one of the existing inputs called "Trips")

2. insert the Query block before the Aggregation and Formula blocks in the Reports, as per image

3. In the Report interface, on the relevant tab, add the Filter devices and expose the fields you wish to influence the calculations. Make sure these are the fields belonging to the original data input (in this case the input called "Trips"). By doing this you are controlling the data flow that is going into the calculations/aggregation, rather than filtering calculation results.

Formula measures

In some cases, it may be useful to add a new 'formula measure' on a particular chart level. When the chart is configured e.g. a Bar chart or Pivot table, they create data subsets where a formula can be easily applied to each 'data bucket', with a different result for each value in a field or each of the multiple field values combinations (Category/Period/Product in the cross-tab). 

To add a formula measure - drag the 'Formula measure' item from the fields list on the left sidebar to the shelf in your chart, to replace the record count (default comparison measure), or slide it next to an existing field measure (below [Sales] sum measure), then click on the f(x) drop menu next to it to write your formula and pick the settings for your result, such as the colour, number of decimals etc.

Calculations in the Content view

Content view in Report area is a powerful tool that combines static and dynamic text, allowing you to create report with narrative that will update itself, with every new batch of data. 

Content view contains tools to link every aspect of the text with the data values - you can colour, size and style text to indicate whether this week's sales are higher or lower than the week before, add coloured arrows etc.

These configuration elements will have the familiar formula-builder interface, however there's one aspect that you need to be aware of - Content view has visibility of all the data that the query provides (filtered/selected/fixed/all) however, there is no way to utilise relative references like data on the same line or the previous line, so you have to use precise referencing in these calculations (e.g. month = Max month or = May).

Useful resources and further reading

Have a look at this article about the Content view in general to learn how to create dynamic text boxes and tables in your report.

Read about the SUBSET syntax and learn how to build formulas in different parts of the application.

There are about 150 different functions in Omniscope - use the search box or filter by type using this interactive functions guide to find the one you need.