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.