Calculated Values

Overview
Calculated values extend SharpCloud stories by deriving new values from existing attributes and properties (similar to spreadsheet formulas).

• Five data types: Number, Date, Text, Boolean, Array.
• Choose the correct return type when creating a calculation — results may be consumed by other calculations.
• Labels (Number, Date, Text) can create buckets used as axes and interactive filters.
• Each function has a dedicated KB article; see the Calculated Values Index for the full list.

Attribute Codes
Calculations reference attributes by their attribute code (alphanumeric, ≤20 characters).

• Set the code in Story Setup → Attributes. Once set, use the code in any calculated value.

Where to Add Calculations
Story Setup → Calculations sub-tab (under Items or Relationships) depending on whether the calculation runs per item or per relationship.

Data Types (summary)

• Number: Integers or decimals (e.g., 1, -4, 1.45).
• Date: Use date attributes or built-in start/end. Create via string (e.g., "12 Dec 2019") or
• DATE(year,month,day). Use TODAY(), ADDDAYS(), ADDMONTHS(), ADDYEARS().
• Valid operations: DATE ± NUMBER (number = days), DATE1 - DATE2 = number of days.
• Invalid: DATE + DATE, NUMBER + DATE, multiplication/division with dates.
• Text: Strings; supports labels and filters.
• Boolean: TRUE/FALSE; supports logic (&&, ||) and comparisons.
• Array: Lists of values; literal form [1,3,5] or ["yes","no"].

Calculation Results & Type Conversions

Choose the expected return type. SharpCloud converts between types per rules (may yield NULL or unexpected results if incompatible).
Key conversions:

• NUMBER → DATE: treated as milliseconds since 1 Jan 1970.
• DATE → NUMBER: date as ticks.
• BOOLEAN → NUMBER: 1 if TRUE, else 0.
• ARRAY → NUMBER/DATE/TEXT/BOOLEAN: single-item array of that type.
• TEXT conversions use TOSTRING() semantics; some conversions can be NULL if unparsable.
  • Example: Calculation = name returns TEXT; if return type set to BOOLEAN it’s TRUE unless name equals FALSE/F/N/NO.

Labels
Apply labels to Number, Date, Text calculated values to create buckets for views and interactive filters.

Operators

Arithmetic
- +, -, *, /, %, ^ — operate on numbers. Return a NUMBER or DATE (when date arithmetic applies).
Date rules:

• • Date + Number → Date (days)
  • Date - Number → Date
  • Date1 - Date2 → Number (days; sign depends on order)
  • Number + Date or Date + Date → invalid

Comparison & Logical
Logical: && (AND), || (OR).
Comparison: ==, !=, >, <, >=, <= (use == for equality).
Return: Boolean. Compare like types (Number vs Number, Date vs Date, Text vs Text).

Parentheses & Brackets
() — control evaluation order.
{} — behave like () but in WHERE they evaluate per-item before the overall formula.
[] — denote arrays.

Core Functions & Properties (selected highlights)
WHERE(condition) → ARRAY of item names/IDs that satisfy condition. Use { } to evaluate per-item values inside WHERE.
GETITEMVALUE(itemIdentifier, codeValue[, type]) → returns a single attribute/calculated value from another item (name default; type can be internal/external id).
GETITEMVALUES([itemIdentifiers], codeValue[, type]) → returns an array of values from multiple items.
RELATEDITEMS([type, none,to,from,both]) → ARRAY of related items (name/internal/external id).
GETRELATIONSHIPVALUES(itemIdentifiers, codeValue[, type]) → returns relationship property values (array).
IF(condition, true_result, false_result) → returns one of two values; return type follows the chosen results.
DATE(year,month,day), TODAY(), ADDDAYS(date,days), ADDMONTHS(date,months), ADDYEARS(date,years)
SUM(values), AVG(values), MAX(values), MIN(values), STDEV(values)
CONCAT(array), LEN(string|array), SUBSTR(string,pos,len), REPLACE(string,search,replace)
INDEXOF(array|string, value), LOOKUP(array,index), INDEXOFLIST(listCode)
LABELS(attributeName) → array of label strings
TAGLIST([tagGroup]) → array of tags
HASTAG(tagName) → Boolean
TOARRAY(text) → converts a string representation into an array
TOUPPER(string), TOLOWER(string)
UNION(array1,array2), INTERSECT(array1,array2)

Statistical/math:

ABS(value), SQRT(value), ROUND/ROUNDDOWN/ROUNDUP(value)

Properties available as functions (items only unless noted)
name (TEXT), description (TEXT), category (TEXT), created (DATE), lastedited (DATE), lasteditedby (TEXT), owner (TEXT)
start (DATE), end (DATE), durndays (NUMBER), durnmnths (NUMBER)
likecount, dislikecount, tagcount, relcount, rescount (NUMBER)
itemtype, storyid (TEXT)
Relationship-only: ITEM1(...), ITEM2(...), ITEMSTART(...), ITEMEND(...), relsfrom/relsto/relsnone/relsboth (NUMERIC)

Examples (concise)
Duration in days: end - start
Overspend: Cost - Budget
Day-of-week offset: (start - DATE(2019,11,20)) % 7  → returns 0–6
Children costs sum: SUM(GETITEMVALUES(WHERE(Parent=={name}), 'Cost'))
Parent total from children: SUM(GETITEMVALUES(RELATEDITEMS(), 'Cost'))
Popularity label: IF(likecount > 5, "Popular", "Not Popular")

Calculated Value Groups & “Included In”
Groups: assign a group name on a calculated value to organize attributes (e.g., Finance, Project).
Included In: limit which categories the calculation runs for to improve performance.

• By default calculations run for all categories; excluded items return NULL unless data from them is required by the calculation (e.g., GETITEMVALUES, WHERE), in which case SharpCloud will evaluate those items as needed.

Notes & Best Practices
Always set the expected return type to match downstream usage.
Prefer using curly braces in WHERE when comparing an attribute of other items to the current item’s attribute (e.g., WHERE(Parent=={name})).
Minimize Included In scope for performance.
Use labels to convert numeric/date/text results into buckets for filtering and visualization.