There are 5 Calculated Values data types that can be created and implemented in SharpCloud.
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.
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.
A text calculation is a string of characters. Text also supports labels and can be used in the Interactive Filters.
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”
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.
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.
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
Date based the number as ticks.
NULL, unless the result can be parsed as a DATE
TRUE IF nonzero, else FALSE
TRUE unless uppercase TEXT is any of ['FALSE','F', 'NO' or 'N']
An array with one NUMBER item
An array with one DATE item
An array with one TEXT item
An array with one BOOLEAN item
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.