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.


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 the same for every record that has a given value in the [Week] field - see image.

This is worth remembering when this field is used in subsequent analysis, to avoid double-counting.


Subset formulae could contain multiple 'open criteria':

SUBSET_SUM([Sales], INTERSECTION( SUBSET([Week]), SUBSET([Store Type])))

This will create a different result variation for each combination of week/store type.


SUBSET_SUM([Sales]) will return sum of all sales, for the whole dataset - no grouping here.


We can also create more targeted conditional criteria:

SUBSET_MEAN([Sales], 

  INTERSECTION(
    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 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 using SUBSET formulas to build Formula measures you have to be aware 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 [Sales] totals on the Y axis as your measure, there is no need to use Subset([Week]) as 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.


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 with 'Pick 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.

 

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.


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 table, 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_MAX([Value], SUBSET([Date],

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

))


#Increase rate:

(SUBSET_MAX([Value])

-

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

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

)))

/

SUBSET_MAX([Value], SUBSET)