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
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 the new formula fields, just like any modifications, are highlighted yellow, so anyone checking/editing the formulas won't have any trouble finding them.
Report level calculations
In the Evo application it is possible to create multiple dashboards in 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
- Variables – incredibly useful tool to allow the end user to control the dashboard by interacting with numerical/date/text variable, 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 or market price, and watch how different independent variable values would affect the outcome (see demo). Another use case is situation where the dataset contains multiple category fields that the user may wish to use for comparisons, 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 that differ only in the choice of 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. analyse 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 queries driven by the end user. If a query is added in the Report data sources area, before the Aggregation or Formula blocks (see the images below), 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 smaller number of functions, compared to the Field Organiser - these are optimised for the big data use and complemented by the functions in the Aggregation block.
Report data sources offers powerful advanced functionality by combining the Formula, Aggregation, Query and Join blocks to create a central calculation and querying resource in a report for multiple visualisations. Results can be multiple new data sources and multiple new fields, in each data source. Unlike the fields created in the ETL phase, these new sources/fields are only accessible inside the given report.
Chart Level calculations: Formula measure
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 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 a shelf in your chart, to replace the record count (the 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, like the colour, number of decimals etc.