Calculated Values — Data Types

 

Overview
SharpCloud supports five Calculated Value data types: Number, Date, Text, Boolean, Array.
Choose the correct result type when creating formulas; results may be consumed by other calculations.
Labels (buckets) are supported for Number, Date, and Text types and can be used as axes and interactive filters.

 

Data Types

1. Number

• Integers or decimals (e.g., 1, -4, 1.45, 27.634).
• Can be converted to Text and Color via labels.

2. Date

• Use date attributes or built-in properties: start, end.
• Create dates from strings (e.g., "12 Dec 2019", "Dec 19" — assumes 1st of month).
• Build dates from numbers with DATE(year, month, day) e.g., DATE(2019, 12, 2).
• Supported date functions: TODAY(), ADDDAYS(), ADDMONTHS().
• Rules and valid operations:
  • DATE1 - DATE2 = number of days (Date1 earlier gives negative result; Date2 - Date1 gives positive)
  • DATE + NUMBER = DATE (number treated as days)
  • DATE - NUMBER = DATE
  • DATE + DATE = invalid
  • NUMBER + DATE = invalid
  • Multiplication and division are not valid for dates
  • Date type can have labels to create ranges (e.g., short term, medium term, long term).

3. Text

• Any string of characters.
• Supports labels and can be used in Interactive Filters.

4. Boolean

• TRUE or FALSE (YES or NO).
• Returned by some functions and used as parameters in others.
• Can be typed directly as TRUE or FALSE.
• Supports Boolean logic: AND (&&), OR (||).
• Comparison operators (<, <=, >, >=, ==, !=) return Boolean.

5. Array

• A list of numbers, dates, text, or Boolean values.
• Created with square brackets: [1, 3, 5, 6], ["yes","no","maybe"].
• Returned by some functions and passed into others.

 

Calculation Results and Type Conversions
When a calculation runs, SharpCloud returns a type. If types differ, conversions occur per rules below.
Conversions summary (result type when input type is rows/columns — simplified):

  From NUMBER:
  NUMBER => NUMBER
  DATE => number of milliseconds since 1 Jan 1970
  TEXT => NULL unless convertible
  BOOLEAN => 1 if TRUE, else 0
  ARRAY => length of array
  NULL => NULL

  From DATE:
  NUMBER => date based on ticks
  DATE => DATE
  TEXT => NULL unless convertible to DATE
  BOOLEAN => NULL
  ARRAY => NULL
  NULL => NULL

  From TEXT:
  NUMBER => TOSTRING()
  DATE => TOSTRING()
  TEXT => TEXT
  BOOLEAN => TOSTRING()
  ARRAY => TOSTRING()
  NULL => NULL

  From BOOLEAN:
  NUMBER => TRUE if nonzero, else FALSE
  DATE => TRUE
  TEXT => TRUE unless uppercase TEXT is one of ['FALSE','F','NO','N']
  BOOLEAN => BOOLEAN
  ARRAY => TRUE
  NULL => FALSE

  From ARRAY:
  NUMBER => array with one NUMBER item
  DATE => array with one DATE item
  TEXT => array with one TEXT item
  BOOLEAN => array with one BOOLEAN item
  ARRAY => ARRAY
  NULL => NULL

Example: Calculation equals item name (name). If result type TEXT → expected. If result type BOOLEAN → returns TRUE unless the name equals 'NO'/'N'/'FALSE'/'F'.

Calculated Value Labels
Supported for Number, Date, and Text.
Use labels to define buckets and enable their use as axes and filters.

Operators

1. Arithmetic

• Used on numbers (and dates in specific combinations).
• Operators: + (add), - (subtract), * (multiply), / (divide), % (modulus), ^ (power).
• Return: NUMBER or DATE (depending on operands).
• Notes for dates:
  • Date + Number => valid (number = days)
  • Number + Date => invalid
  • Date + Date => invalid
  • Date1 - Date2 => number of days (sign depends on order)
  • Number - Date => invalid
  • Example: Cost - Budget (overspend)
  • Example: end - start (duration in days)
  • Example: (start - DATE(2019,11,20)) % 7 — returns 0–6 for day-of-week offset

2. Comparison and Logical Operators

• Return: Boolean.
• Logical: && (AND), || (OR).
• Comparison: == (equal), != (not equal), >, <, >=, <=.
• Use '==' for equality (single '=' causes error)
• Comparisons should be between same data types (number vs number, date vs date, text vs text).
  • Underlying JS may allow mixed-type comparison but avoid it.
    Example: IF(likecount > 5, "Popular", "Not Popular")

Parentheses and Bracket Types

• Normal parentheses () — change evaluation order.
• Curly braces {} — behave like parentheses but in WHERE clause they force evaluation per item before the rest of the formula.
  • Example: WHERE(parent={name})
• Square brackets [] — denote arrays/collections. Separate items with commas.
  • Example: [1, 2, 3, 4]
  • Operator precedence example: 3+6/2 = 6 (3 + (6/2)); (3+6)/2 = 4.5.

Quick Examples
Arithmetic: 2 == 3 → FALSE; 2 != 3 → TRUE
Labels & Popularity: IF(likecount > 5, "Popular", "Not Popular")
Date duration: end - start
Day-of-week offset: (start - DATE(2019,11,20)) % 7
Array literal: [1,3,5,6]