There are 5 Calculated Values data types that can be created and implemented in SharpCloud.

• Number
• Date
• Text
• Boolean
• Array

Number

An integer (1, 2, 20, -4 etc.) or decimal (1.45, 27.634 etc.). The number can be converted to a text value and colour using the labels feature.

Date

You can use date attributes, or the built-in properties of 'start' and 'end'.

You can create a date using a simple string format E.g. '12 Dec 2019', or even 'Dec 19' (where the 1st of the month is assumed.

Use the DATE() function to build a date out of NUMBERS E.g. DATE(2019, 12, 2) for '2 Dec 2019'

Note that unlike NUMBERs there are rules about the order of calculation for dates.

DATE1 - DATE2 = NUMBER OF DAYS (where Date1 is chronologically earlier than Date2)

DATE2 - DATE1 = -NUMBER OF DAYS (where Date1 is chronologically earlier than Date2)

DATE + DATE = INVALID

NUMBER + DATE = INVALID

DATE + NUMBER = DATE (Number assumed to be days).

Multiply and Divide are not valid operators.

DATE type can also be converted to a list by adding labels. This is a powerful way to create a range like  short term, medium term and long term.

Text

A text calculation is a string of characters. Text also supports labels and can be used in the Interactive Filters.

Boolean

Returns a TRUE or FALSE (YES or NO). Boolean values are returned by some functions and required as parameters to others, as well as being the result of the comparison operators (<,<= etc). SharpCloud also supports Boolean logic (AND, OR).  You can also refer directly to these values by typing “TRUE” or “FALSE”

Array

An array is a list of numbers, dates, text or Boolean values. Arrays are returned by some functions and are passed into others as parameters. You can create your own array using the square bracket notation. E.g. [1,3,5,6] is an array of numbers, [“yes”, “no”, “maybe”] is an array of text.

Calculation Results

When you create your formula, you must decide on the type of result you expect the calculation to return. Since each calculation can be used by other calculations it is important that the result has the right type. SharpCloud will always try its best to give you results that make sense, but if the return types are not what is expected a conversion will need to be performed and you may get unexpected results.

Below is the table of conversions. Note that the grey boxes are given as the result and the types match.

 CALCULATION RESULT Data Type NUMBER DATE TEXT BOOLEAN ARRAY NULL NUMBER NUMBER NUMBER OF Milliseconds since 1 Jan 1970 NULL, unless the result can be passed as a NUMBER 1 if TRUE, otherwise 0 The length of the array NULL DATE Date based the number as ticks. DATE NULL, unless the result can be parsed as a DATE null null NULL TEXT TOSTRING() TOSTRING() TEXT TOSTRING() TOSTRING() NULL BOOLEAN TRUE IF nonzero, else FALSE TRUE TRUE unless uppercase TEXT is any of ['FALSE','F', 'NO' or 'N'] BOOLEAN TRUE FALSE ARRAY An array with one NUMBER item An array with one DATE item An array with one TEXT item An array with one BOOLEAN item ARRAY NULL

Example -

The calculation is simply ‘name’ (i.e. the items name). The return type of TEXT will give expected results because name is also a TEXT. If the Calculation return type is set to BOOLEAN (see yellow cell above), the result will always be ‘TRUE’, unless the item name is ‘NO’/’N’ or ‘FALSE’/’F’. (Note that item name cannot be NULL).

Calculated Value Labels

Number, Date and Text calculated value data types all support labels, allowing users to define specific label ‘groups’/ ‘buckets’ for the results of the calculation. These labels can then be used as axes on specific views and as interactive filters.