Advanced LiveDataset

Link datasets together to build online systems.

LiveDataset

Using Formulas and Functions

A formula can be used in a calculated column and will be automatically calculated for every row in the dataset.

To enter a formula, the column type must be set to Formula.

When a formula refers to a column, use "col" and the column ID number. In the example above the column ID for Sales is 2, so in the formula we use "col2".

Note: Whenever column is used in a formula, a constant value can also be used.

A formula can be as simple as

  • =100
  • ="Hello World"
  • = col2 + col3

Formulas can contain numbers, text, and functions including

Formula Syntax

  • A formula always starts with "=".
  • Text must be enclosed in double quotes "like this".
  • Columns are referenced using "col" and the column ID number.
  • Arguments for a formula are encased in round brackets ( ) and separated by commas.

    e.g. =ROUND(col2,1) will take values from the column that has ID 2 and round them to 1 decimal place.

  • The text operator "+" can be used to concatenate text values in columns and text constants

    Example: if col1 contains "Live" then the result of formula =col1+"Dataset" is "LiveDataset"

Simple math operators can be used

Operator Description
+ Add
- Subtract
* Multiply
/ Divide
() Use to specify precedence

Precedence examples:

  • =100+400/200 following normal mathematical convention, division takes precedence and is calculated first
    =100+2 =102

  • =(100+400)/200 as ( ) specifies the addition should take precedence it is calculated before dividing by 200
    =500/200 =2.5

Math Functions

Math functions should be used on columns containing numbers.

Function Example
ABS(column)
Absolute values of numbers in the column.
Formula: =ABS(col1)
col1 result
-21.5 21.5
17.31 17.31
44 44
CEILING(column)
Rounds up numbers in the column.
Formula: =CEILING(col1)
col1 result
-21.5 -22
17.31 18
44 44
FLOOR(column)
Rounds down numbers in the column.
Formula: =FLOOR(col1)
col1 result
-21.5 -21
17.31 17
44 44
MAX(column, column)
The maximum value from the columns.
Formula: =MAX(col1,col2)
col1 col2 result
-21.5 79 79
17.31 -8.01 17.31
44 49.45 49.45
MIN(column, column)
The minimum value from the columns.
Formula: =MIN(col1,col2)
col1 col2 result
-21.5 79 -21.5
17.31 -8.01 -8.01
44 49.45 44
ROUND(column)
Rounds numbers in the column to the nearest integer.
Formula: =ROUND(col1)
col1 result
-21.5 -22
17.31 17
44 44
ROUND(column,[places])
Rounds numbers in the column to the number of places specified.
Formula: =ROUND(col1,1)
col1 result
-21.5 -21.5
17.31 17.3
44 44
VALUE(column)
Extracts the numerical value from text.
Formula: =VALUE(col1)
col1 result
£1000 1000
-$345,832 -345832
44 Days 44

Text Functions

Text functions should be used on columns containing text.

Function Example
LEFT(column, length)
The first text characters from text values in the specified column.
length specifies the number of characters.
Formula: =LEFT(col1,4)
col1 result
LiveDataset Live
Spreadsheet Spre
Database Data
MID(column, start, length)
Returns text characters from the specified column.
start specifies the starting point.
length specifies the number of characters.
Formula: =MID(col1,5,4)
col1 result
LiveDataset Data
Spreadsheet adsh
Database base
RIGHT(column, length)
The last text characters from the end of text values in the specified column.
length specifies the number of characters.
Formula: =RIGHT(col1,3)
col1 result
LiveDataset set
Spreadsheet eet
Database ase
TRIM(column)
Removes leading and trailing spaces from a specified column.
Formula: =TRIM(col1)
col1 result
 LiveDataset  LiveDataset
Spreadsheet  Spreadsheet
 Hello World Hello World
SEARCH(column, column, [startnum])
The index of the first column text inside the second column text.
startnum specifies the start position in the text to search. Optional, defaults to 1 if not specifed.
Formula: =SEARCH(col1, col2)
col1 col2 result
Data LiveDataset 5
read Spreadsheet 3
E Database 8
TEXT(column)
Converts numbers into text.
Formula: =TEXT(col1) + col2
col1 col2 result
2346 31 234631
23 0 230
480 29 48029
SUBSTITUTE(text, to_replace, replace_with, [nth])
text, to_replace, replace_with are either fixed text, or a text column. nth is an integer.
Replaces (substitutes) occurrences of what is specified in the to_replace parameter that is found within the text parameter with what is specified in the replace_with parameter.
If nth is not present, it will replace all occurrences, if it is present, it will only replace the nth occurrence, where the first occurance is 1.
Formula: =SUBSTITUTE(col1, col2, col3)
col1 col2 col3 result
abcb b d adcd

Formula: =SUBSTITUTE(col1, col2, col3, col4)
col1 col2 col3 col4 result
abcb b d 1 adcb
abcb b d 2 abcd

Date Functions

Date functions should be used on columns containing dates.

Function Example
DATEDIF(column, column, value)
The number of seconds, minutes, hours, days, months or years between dates.
Second column dates are 'subtracted' from first column.
value must be one of:
  • "S" for seconds
  • "Mi" for minutes
  • "H" for hours
  • "D" for days
  • "M" for months
  • "Y" for years
Formula: =DATEDIF(col1,col2,"M")
col1 col2 result
2016-12-31 2017-12-31 -12
2016-02-29 2012-12-31 48
2000-10-29 2000-12-29 -2
Formula: =DATEDIF(col1,col2,"H")
col1 col2 result
2016-12-31 05:14 2016-12-31 10:36 -5
2016-02-29 17:52 2016-02-28 15:42 26
2000-10-29 11:39 2000-10-30 03:28 -15
MINUTE(column)
The minute of the hour as a number.
Formula: =MINUTE(col1)
col1 result
2016-12-31 05:14 14
2016-02-29 17:52 52
2000-10-29 11:39 39
HOUR(column)
The hour of the day as a number.
The value will be displayed in UTC time zone for consistency across users.
Formula: =HOUR(col1)
col1 result
2016-12-31 05:14 5
2016-02-29 17:52 17
2000-10-29 11:39 11
DAY(column)
The day of the month as a number.
Formula: =DAY(col1)
col1 result
2016-12-31 31
2016-02-29 29
2000-10-29 29
DAYADD(column, value or column)
Adds a number of days to a date.
Use negative values to subtract days.
Formula: =DAYADD(col1,col2)
col1 col2 result
2016-12-31 31 2017-01-31
2016-02-29 0 2016-02-29
2000-10-29 -29 2000-09-30
TIMEADD(column, value or column, value or column)
Adds a number of hours and minutes to a date.
Use negative values to subtract days.
Formula: =TIMEADD(col1,col2,col3)
col1 col2 col3 result
2016-12-31 05:14 7 20 2016-12-31 12:34
2016-02-29 17:52 0 7 2016-02-29 17:59
2000-10-29 11:39 -11 0 2000-10-29 00:39
MONTH(column)
The month for the dates in the column as a number.
Formula: =MONTH(col1)
col1 result
2016-12-31 12
2016-02-29 2
2000-10-29 10
WEEKDAY(column)
The day of the week for the dates in the column.
result a number from 1 to 7.
Formula: =WEEKDAY(col1)
col1 result
2016-12-31 7
2016-02-29 2
2000-10-29 1
WEEKNUM(column)
The number of the week for the dates in the column.
result a number from 1 to 53.
Formula: =WEEKNUM(col1)
col1 result
2016-12-31 53
2016-02-29 10
2000-10-29 45
YEAR(column)
The year for the dates in the column as a number.
Formula: =YEAR(col1)
col1 result
2016-12-31 2016
2016-02-29 2016
2000-10-29 2000

List and Multiselect Functions

List and multiselect functions should be used on columns containing lists or multiple values.

Function Example
COUNTMATCHES(column, value...)
The number of values in the column that match the list provided.
Formula: =COUNTMATCHES(col1,"a","b","c")
col1 result
a,b 2
c,d 1
a,b,c,d 3
d,e 0
SIZE(column)
Number of values in the column.
Formula: =SIZE(col1)
col1 result
a,b 2
c 1
a,b,c,d 4
0

Logical and Lookup Functions

Function Example
IF(condition, true, false) Tests a condition and returns value from true or false.

condition must include one of:
  • == for equals.
  • != for not equals.
  • > for greater than.
  • >= for greater than or equals.
  • < for less than.
  • <= for less than or equals.
  • ISEMPTY(column) to check for empty values.
  • ISNUMBER(column) to check if a value is a number.
  • MATCHESANY(column, value…) to check if any of the values in the column are in the list provided.
  • MATCHESALL(column, value…) to check if all of the values in the column are in the list provided.
conditions can be combined using:
  • AND to test for 2 conditions to both be true.
  • OR to test for at least one of the conditions to be true.
  • XOR to test for exactly one condition to be true.
  • NOT to test for the condition to NOT be true.
true is the value to return if condition is met.
false is the value to return if condition is not met.

TIP You can compare dates, numbers, and text.
Formula: =IF(col1>100,col1/100,0)
col1 result
250 2.5
 
89 0
Formula: =IF(ISEMPTY(col1),"??",col1)
col1 result
250 250
  ??
89 89
Formula: =IF(col1<100 AND NOT ISEMPTY(col1),"ok","")
col1 result
250
 
89 ok
Formula: =IF(MATCHESANY(col1,"a","b","c"), "true, "false")
col1 result
a,b true
c,d true
a,b,c,d true
d,e false
Formula: =IF(MATCHESALL(col1,"a","b","c"), "true, "false")
col1 result
a,b true
a,b,c true
c,d false
a,b,c,d false
d,e false
LINK(url)
A hyperlink to the url. url can be constructed using other functions.
Formula: =LINK("http://report.com?id="+col1)
col1 result
250 http://report.com?id=250
  http://report.com?id=
89 http://report.com?id=89
VLOOKUP(dataset.column, criteria) Returns values from another dataset where criteria match between the current dataset and the other dataset.

dataset.column is specified as "ds"dataset id."col"column id
e.g. use ds111.col2 for column 2 in dataset 111.

criteria is specified as dataset.column=column to find matching values between a column in the other dataset with a column in the current dataset.

You can include multiple criteria.

TIP Be careful to avoid creating a circular reference.
Other dataset (dataset id=111)
col1 col2
France €EUR
Germany €EUR
UK £GBP
USA $USD
Current dataset Formula:
=VLOOKUP(ds111.col2,ds111.col1=col1)
col1 col2
USA $USD
France €EUR
USA $USD
In this example, dataset 111 holds currencies for countries. The lookup formula matches col1 in the current dataset with col1 in dataset 111 and returns the value of col2 in dataset 111 for that row.

So "USA" in current dataset is looked up in currencies dataset and returns "$USD".

This is similar to performing a join between database tables.

TIP A quick way to find a dataset id is to look at its url.