Dashboard formulas

There are 2 kinds of formulas: Compute and Rendering.

Use a Compute formula if you want to apply a calculation on the cell value. Excel exports will use the results of the calculation. If you want to apply a formula to change the cell presentation (e.g. icons, html code), use a Rendering formula. This calculation won't be applied when exporting data to Excel.

Note that the data is first transformed by the Compute formula, the result is then passed to the numeral formatting (if there is one), and then the result is passed to the Rendering formula.

Numeral formatting and rendering formula

If you apply a numeral formatting to your data (e.g. 1 000 or 12.34%), this formatting is applied to the data before it is passed to the rendering formula. For example, if the input data is the number 67425 and the numeral formatting is 1 000 then the input of the rendering formula will be 67 425.

This value is a string containing a white space. If you use a conditional formula (e.g. value > 50000 ? "large" : "small"), it won't work. The condition will always be true.

You need to convert the input string to a number with the toNumber() function (e.g. toNumber(value) > 50000 ? "large" : "small").

Variables available in a formula

  • value is the current cell value.
  • line[N] is the value of the cell of the Nth column on the current line (first column is line[1]).
  • aboveLine[N] is the value of the cell of the Nth column on the line above the current line.
  • belowLine[N] is the value of the cell of the Nth column on the line below the current line.
  • lineIndex is the line index in the array (first line has index 1).
  • isFirstLine true if the current cell is in the first line of the array.
  • isLastLine true if the current cell is in the last line of the array.

In free-tables

There is an extra variable available only in free-table that lets you access the value of any cell in the table:

  • data[rowId, columnId] is the value of the cell at row rowId and column columnId. data[1,1] is the top-left cell.

In bar charts / curve charts

In a formula, line[1] stands for the X axis, and line[2], ... line[N+1] are the N selected measures.

Example:

If you selected 2 measures Margin and Sales, you can enter in the field "Computed formula" the following formula:

1
line[2] / line[3] * 100

to get a percentage of the margin vs the sales. It's also possible to hide the Sales measure if you only want to display the percentage by clicking on "Hide measure".

Split by mode

Using multiple measures (and so also formulas) is not available when using "Split by" mode

Note about variables

The value(s) available with one of the variables above (e.g. value, line, data...) is the output of the Compute formula. The Rendering formula is not applied before returning a variable value.

Examples of Compute Formulas

  • Simple formula
1
value / 2
  • Column 2 divided by column 1
1
line[2] / line[1]
  • Simple conditional
1
value >= 20 ? "large" : "small"
  • Evolution from line below
1
isLastLine ? "n.a." : (line[1] - belowLine[1])/belowLine[1]
  • Accumulation of column 1 values in column 2
1
aboveLine[2] + line[1]
  • Number of days between a date and now
1
 date_diff(value, now())

Examples of Rendering Formulas

  • Conditional with icon
1
value >= 20 ? (icon("arrow-up", "green") + value) : (icon("arrow-down", "red") + value)
  • Evolution from line below with icons
1
2
3
4
isLastLine ? "n.a." : (line[1] > belowLine[1]) ?
("+" + round((line[1] - belowLine[1]) / belowLine[1] * 100, 2) + " % " + icon("arrow-up", "green"))
:
(round((line[1] - belowLine[1]) / belowLine[1] * 100, 2) + " % " + icon("arrow-down", "red"))

String Syntax

String must be within double quotes (e.g.: replace(value, "k", "kilo") and not within simple quotes.

Available functions

icon()

Inserts an icon with the given color. See here for the full icon set.

Parameters:

  • icon_name: icon to use
  • color[optional]: icon color.

Example:

1
icon("flag", "red")

Inserts the link text pointing to url.

Parameters:

  • text: text to display
  • url: url to open
  • mode[optional]: can be "same" or "new".

If mode is "new" or not set, the link will be opened in a new window. If mode is "same", the link will be opened in the same window when clicked.

Example:

1
link("Serenytics", "https://www.serenytics.com", "new")

Inserts the link text pointing to the dashboard with uuid uuid, with the option to filter the destination dashboard with a payload.

Parameters:

  • text: text to display
  • uuid: uuid of the dashboard to link to.
  • mode[optional]: can be "same" or "new".
  • payload_filter[optional]: the payload field name.
  • payload_value[optional]: the payload value for payload_filter.

If mode is "new" or not set, the link will be opened in a new window. If mode is "same", the link will be opened in the same window when clicked.

If both payload_filter and payload_value are defined, the payload {payload_filter: payload_value} will be passed to the dashboard. This payload can be used in a global static filter (see template dashboard).

Example:

1
2
3
4
5
link_to_dashboard("User info", "176a8ac8-5c85-4fd0-b960-bad858f986e0", "new", "user_id", 10)
// the payload passed to the dashboard will be {"user_id": "10"}

link_to_dashboard("User info", "176a8ac8-5c85-4fd0-b960-bad858f986e0", "new", "user_id", line[1])
// this example shows how to filter the destination dashboard with the user id contained in line[1]

create_source_file_url()

This function is obsolete and will be removed soon

To allow full download of a datasource in a dashboard by a Viewer, please read the doc here.

Returns an URL to download the file of the source with uuid uuid. The returned URL is valid only if the data source is used at least within one widget of the dashboard (this is a security constraint) and the option Allow download file in viewer must be enabled on the data source.

Parameters:

  • uuid: uuid of the data source.

Example:

1
create_source_file_url("176a8ac8-5c85-4fd0-b960-bad858f986e0")

How to use create_source_file_url:

Create a KPI widget, select the source you want to download in its data configuration. In the rendering section, choose the template Custom HTML. Then paste this HTML code:

1
2
3
<div style="padding:20px; background-color: lightblue; height: 100%;font-size: 2vw">
    <a href={{create_source_file_url("413faa5a-e859-cf99-a1be-e0ff63878a82")}}>Download file</a>
</div>

Error handling for create_source_file_url

If you have issues using this function, open the developer console of your browser. Errors from this function are displayed there.

mailto()

Shows a link to send an email to the recipient email, with subject subject and email body body.

Parameters:

  • text: text to display as a link
  • email: email recipient
  • subject: email subject
  • body: email body

Example:

1
mailto("Send Email", "contact@serenytics.com", "Email from dashboard", "Hello<br>Can you update your settings?")

replace()

Replaces string s1 by string s2 in string str.

Parameters:

  • str: input string
  • s1: string to replace
  • s2: new string
1
replace("2016-02-28", "-", "/")

repeat()

Repeats the str string N times.

Parameters:

  • str: string to repeat
  • N: number of times to repeat

Example:

1
repeat(icon("star", "orange"), 3)

padLeft()

Pads str on the left side with chars if it's shorter than length.

Parameters:

  • str: input string
  • length: output string length
  • chars[optional]: characters to use to pad the input string

Example:

1
padLeft(value, 2, "0")

round()

Rounds the value val to N decimals.

Parameters:

  • val: input value
  • N: number of decimals in the output value

Example:

1
round(value, 2)

floor()

Rounds the value val to the closest integer downward.

Parameters:

  • val: input value

Example:

1
floor(value)

fix()

Rounds the value val to an integer toward zero.

Parameters:

  • val: input value

Example:

1
fix(value)

abs()

Returns the absolute value of val.

Parameters:

  • val: input value

Example:

1
abs(value)

pow()

Calculates the power of x to y.

Parameters:

  • x: input value
  • y: power to use

Example:

1
pow(value, 2)

sqrt()

Computes the square root of val.

Parameters:

  • val: input value

Example:

1
sqrt(value)

sign()

Computes the sign of val, either -1,+1 or 0 (if val is 0).

Parameters:

  • val: input value

Example:

1
sign(value)

now()

Returns the current datetime, in UTC in ISO format.

Example:

1
now()

formatDate()

Formats inputDateString with the format given by outputFormatString (e.g. "YYYY - MM - DD", "LLLL"). IfinputDateString is not in ISO format, specify its format with inputFormatString (e.g. "YYYY-MM-DD", see here for all possible values). See here for a complete documentation about available output formats.

Parameters:

  • inputDateString: input string
  • outputFormatString: output string format
  • inputFormatString[optional]: input string format

Example:

1
formatDate(value, "YYYY - MM - DD", "YYYY/MM/DD")

formatDateWithLocale()

Use this function instead of formatDate when you want to set the locale to use for the output format.

Parameters:

  • inputDateString: input string
  • outputFormatString: output string format
  • locale: locale to use
  • inputFormatString[optional]: input string format

Example:

1
formatDateWithLocale(value, "LLLL", "fr")

date_diff()

Returns date2 - date1 in days.

Parameters:

  • date1: first date
  • date2: second date

Example:

1
date_diff(line[1], line[2])

dateAdd()

Add (or substract) a period of time to a date (or datetime).

Parameters:

  • inputDate : input date
  • nbUnits : number of time units to add (or substract)
  • timeUnit : time unit, can be "years", "quarters", "months", "weeks", "days", "hours", "minutes", "seconds" or "milliseconds"
  • outputFormat[optional] : output format (default format is 'YYYY-MM-DD')

Example:

1
2
dateAdd("2017-06-21", 2, "months") // returns 2017-08-21
dateAdd("2017-06-21", -1, "years") // returns 2016-06-21

toNumber()

Convert val to number.

Parameters:

  • val: value to convert

Example:

1
2
toNumber("12.34%") // returns number 12.34
toNumber("367 345 euros") // returns number 367345