Omniscope allows you to perform calculations in multiple locations, as discussed in this article, however the context and exact application might affect the syntax or the results.

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

Subset is method of dividing the dataset into different groups. You could divide some transactional data according to the week number into Week 1, Week 2 and so on. This is the basis that enables us to perform multiple statistical functions and end up with different results for each of these subsets e.g. if we add up the sales data we might conclude that the sum of sales in week 1 is higher than the sum of sales in week 2.

a) Typical syntax contains in the first part the name of the statistical function and in the second part the subset division criteria

SUBSET_SUM([Sales], SUBSET([Week]))

In other words - create a different sum of sales for every week in the dataset.

When applied in a tabular environment (in the Field Organiser block or Report data sources), where the data is organised in a table, result will be calculated for each record for the [Week] value on the given row - therefore will return the same result [Weekly sales] for every record that has the same value in the [Week] field - see image.

This is worth remembering when [Weekly sales] is used in subsequent analysis, to avoid double-counting! 

b) Subset formulae could contain multiple 'open criteria':



( SUBSET([Week]), SUBSET([Store Type])))

This will create a different result variation for each combination of Week/ Store values.

c) Below formula will return sum of all sales, for the whole dataset - no grouping here.


d) We can also create more targeted conditional criteria:


    SUBSET([Traffic], 1000, ">"),

    SUBSET(Store Type], "CONTROL", "=")



Calculate a mean for the subset of the dataset where the [Store Type] is "CONTROL" and the Traffic is higher than 1000. Note how in the syntax the text field values have to have "" around them, while you do not need to do that with the numeric fields' criteria.

This type of syntax is precisely referring to certain records according to the value in particular fields, regardless of their position in a table. This is why this type of subset calculation could be applied in all scenarios listed above, including the Content or KPI view. 

When building formulas for these two views it is good to know that the underlying data query has no perception of the record, therefore relative references to 'current row value', e.g. referring to the current row week and store combination, will not work. Subset references have to be precise and define which data subset the calculus will be applied to (like in the d example).

When using SUBSET formulas to build Formula measures it is worth remembering that the chart will divide the records automatically into 'buckets' e.g. your Bar chart might have [Week] on the X axis. If you wish to use [Profit] or  [Units] as your measure, there is no need to add Subset([Week]) as a subset grouping condition - the chart has already done that for you, which is convenient. That nicety comes with a limitation that the data placed in a 'Week 1 bucket' has no visibility of the data in other weeks, so you could not reference them in your formula syntax e.g. divide sales in one week with the sum of sales in all weeks.

#Calculating a difference in [Amount] for 2 consecutive periods (for the consecutive period difference calculations e.g. DoD, WoW, MoM, where [Rank date] could be replaced by the week or month number):

SUBSET_SUM([Amount], subset([Rank date]))


SUBSET_SUM([Amount], subset([Rank date],[Rank date]-1))

Formula builder interface

In all the different scenarios Omniscope will offer a helping hand in form of a 'formula builder' interface.  Here you can pick a function, that comes with the syntax, which can be easily edited and items replaced using the 'Insert field' option.

It is obvious that Subset functions are just a subset of ALL the different functions you can perform in Omniscope, although you will probably be familiar with these from Excel or other packages.

Reference 1

Full list of SUBSET functions is below, although you may prefer to search for a function in our interactive Function picker. All these functions will be available in the Field Organiser block, however some of them may not appear in the Content view/ Report Data Sources/ Formula measures.


SUBSET_UNIQUESLIST(stat_field, subset)A comma-separated list of all unique values] of a field.
SUBSET_EMPTYCOUNT(stat_field, subset)Number of empty values] of a field.
SUBSET_NONEMPTYCOUNT(stat_field, subset)Number of non-empty values] of a field.
SUBSET_UNIQUECOUNT(stat_field, subset)Number of unique non-empty values] of a field.
SUBSET_FIRSTNONNULL(stat_field, subset)The first non-null value in the original data order] of a field.
SUBSET_FIRST(stat_field, subset)The first value (or null) in the original data order] of a field.
SUBSET_LASTNONNULL(stat_field, subset)The last non-null value in the original data order] of a field.
SUBSET_LAST(stat_field, subset)The last value (or null) in the original data order] of a field.
SUBSET_SINGLETON(stat_field, subset)The single value, if there is one] of a field.
SUBSET_MAX(stat_field, subset)Maximum value (highest or last)] of a field.
SUBSET_MEAN(stat_field, subset)Mean (average) of values] of a field.
SUBSET_MEDIAN(stat_field, subset)Median (middle) of values] of a field.
SUBSET_MIN(stat_field, subset)Minimum value (lowest or first)] of a field.
SUBSET_MODE(stat_field, subset)Most common value (first if multimodal)] of a field.
SUBSET_PRODUCT(stat_field, subset)Product of values] of a field.
SUBSET_RANGE(stat_field, subset)Range of values (maximum minus minimum)] of a field.
SUBSET_STDDEV(stat_field, subset)Standard deviation of values from the mean] of a field.
SUBSET_SUM(stat_field, subset)Sum (total) of values] of a field.

SUBSET is usually used only as an argument to another function (e.g. SUBSET_SUM), like above, to define the data groupings:
(a) all records (if no arguments are specified); or
(b) a current subset of records (if field is specified); or
(c) another subset of records (if field and field value are specified); or
(d) another subset of records, using a custom operator (if all arguments are specified).

Custom operator should be one of: 

"=", "<>", ">", ">=", "<", "<=", "!=" (not equal), "contains", "does-not-contain", "starts-with", "ends-with".

Reference 2

Interactive Omniscope functions guide link

Reference 3 

Several examples of formulas used in a Content view to produce some KPIs using date referencing.

Note that because of the absence of a tabular structure, referencing is done by using date functions. [Value] field in this case contains some cumulative values.

#Previous day value:

SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))


#Difference from a previous day:



SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))


#Increase rate (difference divided by the previous period):



SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))



SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day")

#Calculate the % difference in [Price USD] between the first and the last day in the dataset