Useful Formulae

Below are some examples of formulae syntax useful for defining commonly-used calculated Formula 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 Add/Edit Formula wizard. Given that every Omniscope file is potentially a template file that will be refreshed with future data from the linked source, it is good practise to always test for null or zero values that may appear in future refreshes, creating errors in formulae.

1. 'Bucketing' values:

To create a new column where values in a source column are categorised 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],"B")<=10),"Top 10",null)

Note: '!=' means 'not equal'.  In this formula, the first two (value) references to column B are selected using the blue Insert field drop down, but the third (field reference to the contents of B) is typed in double quotes. 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.

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.

Back to Functions Guide