How-To

Types of Values

Data values in Sigma each have a type. Some data types are numbers, dates, and text. Each value has a specific type associated with it, and that type governs rules about how it will be displayed and what kind of operations are allowed on it.

Most of the time, types are something that operates in the background of Sigma and you don’t need to worry about them. However, it can be useful to understand types and how to change data types when writing some formulas.

Sigma Types

Numbers: 0, 1, 2.1
Text: "St. Louise"
Dates: Date("2010-10-2")
Logical Values: True and False

Note that values in a Sigma Worksheet are derived from a linked Database which has its own system of types. Database types are intended to optimize the storage of data and are much more complex. Sigma simplifies that by consolidating database types into this short list.

However, if a Database table contains a type that doesn’t correspond to one of Sigma’s own, Sigma treats those values as text. In the future Sigma will support more types of Values as needed.

Dates

Dates in Sigma can represent either a specific calendar date, or a specific date and time.

Sigma does not have distinct time or duration types. Instead you can use functions like DateAdd or DateDiff to work with a quantities of time in a specific unit.

Null Values

Null represents the absence of value. In the worksheet Null is displayed as an empty cell. Null values can appear anywhere for a number of reasons:

  • Null values were already present in the linked database table
  • A formula could not compute a value, like 100 / 0
  • A corresponding row could not be found in a joined table

In many cases passing a Null to a function which is not expecting it will cause a Null result. This can lead to some odd results:

3 = 3         // True
3 = 1         // False
3 = Null      // Null
Null = Null   // Null

This behavior helps Sigma to continue in spite of what would otherwise be an error, at the expense of sometimes obscuring the source of the error. In cases where a possible Null value is expected, functions like IsNull and Coalesce can be helpful.

Null values may be filtered away, just like other types of Values.

Functions and Types

Each function and operator has rules about the types of arguments it allows. If an argument is not of an allowed type, the formula editor will display a warning and the column will be in an error state.

Function documentation will indicate if there are restrictions to argument types that can be used with that function.

Type Conversions

Sometimes a value will not be recognized as the correct type, or a formula requires you to specify the type of entry. Use an appropriate type-conversion function to ensure the data is treated as the correct Type.

Variant Types

Most data warehouses support some form of composite data within columns. (e.g. nested arrays, nested structures, JSON or XML) Sigma refers to these as variants. Currently variant type columns are shown as empty with no ability to inspect or manipulate. Better support for this type of data is forthcoming.