# 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`).
• `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` stands for the X axis, and `line, ... 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 / line * 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

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 / line ```
• Simple conditional
 `1` ```value >= 20 ? "large" : "small" ```
• Evolution from line below
 `1` ```isLastLine ? "n.a." : (line - belowLine)/belowLine ```
• Accumulation of column 1 values in column 2
 `1` ```aboveLine + line ```
• 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 > belowLine) ? ("+" + round((line - belowLine) / belowLine * 100, 2) + " % " + icon("arrow-up", "green")) : (round((line - belowLine) / belowLine * 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) // this example shows how to filter the destination dashboard with the user id contained in line ```

### `btn_run_automation()`¶

Inserts a button triggering an automation. This can be used in a table to add a button per row that triggers a Python script. The button can pass values from the table rows to the Python script.

Parameters:

• `btn_title`: title of the button to display
• `automation_uuid`: uuid of the automation to run
• other args: payload passed as list of key,values args (see example below). Whites spaces in keys or values are not supported and are removed before being passed to the script.

If the automation is a Python script, the payload can be retrieved in the dict `client.script_args`.

Example:

 ```1 2 3 4 5 6 7``` ```// example without payload btn_run_automation("My button", "176a8ac8-5c85-4fd0-b960-bad858f986e0") // example with a payload with two keys // the value associated with myKey1 is the hard-coded string "Content" // the value associated with myKey2 is the variable in column 2 of the table row btn_run_automation("My button", "176a8ac8-5c85-4fd0-b960-bad858f986e0", "myKey1", "Content", "myKey2", line) ```

### `create_source_file_url()`¶

This function is obsolete and will be removed soon

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``` ``` ```

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
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() ```

### `formatNumeral()`¶

Formats a number.

Parameters:

• `inputString`: input value.
• `format`: output format. To be chosen within values listed in menu Numeral (e.g. "1 000", "1 000.23 \$"...).
• `decimal_sep`: decimal separator
• `thousand_sep`: thousand separator
• `currency_symbol`: currency symbol

Example:

 `1` ```formatNumeral(value, "1 000 \$",".", " ","€") ```

### `formatDate()`¶

Formats `inputDateString` with the format given by `outputFormatString` (e.g. "YYYY - MM - DD", "LLLL"). If`inputDateString` 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, line) ```

### `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 ```