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.

 

See also TODAY(), ADDDAYS(), ADDMONTHS() 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.