Useful Formulae

Useful Formulae

Calculating commonly-used results in your files

Below are some examples of formulae useful for calculating commonly-used fields:

Note: You can type function names or pick from the list; text strings in quotes should be entered literally; field names in [    ] should be input using the [Insert field ] tool in the Formula Editor. Given that every Omniscope file is potentially a template file that will be refreshed with future data from the linked source(s), it is good practise to write formulae that always test for null or zero values that may appear in future refreshes, potentially creating errors in formulae.


1. 'Bucketing' or 'Binning' values:

To create a new column categorising values in a source column according to user-defined upper and lower limits:

=IF([SourceColumn]=null,null,IF[SourceColumn]<50,"0-50",IF[SourceColumn]<500,"50-500",">500"))

In this example, each value in [SourceColumn] is evaluated first to see if the value is null, otherwise it is placed in one of three 'bucketing' categories: 0-50, 50-500, or >500

2. Fiscal year 'bucketing':

Dates of payments and other events sometimes need to be categorised by fiscal rather than calendar year.  One way to do this is to use the following formula, which can be extended to any number of years:

=IF([Date]=null,null,IF(DATEDIFF(DATEVALUE("1 Oct 2001"),[Date],"day")<366,"FY2002",IF(DATEDIFF(DATEVALUE("1 Oct 2002"),[Date],"day")<366,"FY2003","Out of range")))

In this example, the company's fiscal year runs from October, and the formula column Fiscal Year will be populated with null, FY2002, FY2003, or "out of range" depending on the value in the [Date] field.

3. Converting/Parsing dates:

Date fields can be translated into various transformations and sub-components by specifying a custom output date format as the second argument in the DATETOTEXT function. For example:

=DATETOTEXT([DateField],"w")  ...this formula evaluates [DateField] and returns the corresponding week number.

=DATETOTEXT([DateField],"EEEE") ...this formula evaluates [DateField] and returns the corresponding day of the week

An especially useful version of this is used to create duplicate date columns typed as Categories which sort:

=DATETOTEXT([DateField],"yyyy-MM-dd") 

You can then Pane views by this categorised date, and use category tick-boxes with the dates naturally in the correct sort order.

4. Categorising by rank:

Often it is useful to categorise records based on their rank in a certain column, i.e. a "Top 10" category. The formula below assigns the category value "Top 10" to any record whose non-null value in column B is ranked 10 or less:

=IF(AND([B] != null, RANK([B])<=10), "Top 10", null)

Note: '!=' means 'not equal'.  Having creating the dynamically-evaluated Category value "Top 10", you can then filter on it and define a Top 10 Named Query data subset such that other views can be set to display only the current Top 10 list of records.

Example File: Ranking Top 10 and Bottom 5  ; Example File: Ranking Data Subsets

5. Flagging extreme values:

Using a Variable to define the threshold, you can flag Values which are outside a user-selectable upper-lower tolerance range:

=IF(OR([Value]<(-1*{Threshold variable}),[Value]>{Threshold variable}),"Flagged","Not Flagged")

Before writing the formula, you must define the Threshold Variable, its min, max and default values

6. Value counts:

Counting the number of times a particular value appears in a column:

=DATASET_NONEMPTYCOUNT("Column Name","Column Name", [Insert field-Column Name])

The first free text input "Column Name" specifies the column, the second the non-empty and the last is an [Insert Field] of the field 'Column Name'  to pick up the values. This admittedly obscure syntax will return for each row the total number of times the value in the row appears in that same column in the entire data set.

7. Converting nulls to zeros:

Sometimes incoming data sets have null values and for aggregation purposes these values need to be converted to zeros:

=SUM([Field containing nulls])

This formula reads the values in any number of fields, and if they are all null, will return a zero for that row

8. Calculating periodic differences in time series

Unlike spreadsheets, Omniscope formulae do not reference cells or rows directly, but common calculations involving periodic differences over time can be done using Date functions and Subset functions, as illustrated by the attached file which calculates price changes on a security which is only traded on weekdays:

Example file - Calculating Periodic Changes over Time