Skip to main content

Data recovery - Formulas

Updated this week

Prerequisite: log in to io-base from the IndaSheet module.

The IndaSheet module gives access to 3 new formulas in Excel:

  • IDB_RANGE : recovery of the data of a metric between two dates

  • IDB_LATEST : recovery of the last value written in base for one or several metrics

  • IDB_INSTANT : retrieve the value entered at a date

  • IDB_DATE : returns a date in ISO format, which can be used in io-base formulas

These formulas can be built using the interface (for more details, please refer to the article on the subject). They can also be entered directly in a cell.

IDB_RANGE formula

Signature of the formula

=iobase.IDB_RANGE(datasource;metric;start_date;end_date;aggregation;[interval];[displayInRow];[displayTimestamp];[displayCount];[displayMetric];[precision])

The last 6 parameters are optional. If they are not filled in, the default values are described below.

Details of the parameters

  • datasource : STRING : name of the database (in general, the production data is in the prod database)

  • metric : STRING : name of the metric

  • start_date : STRING : start date of the range. This date must be in "yyyy-MM-ddThh:mm:ssZ" format (iso format).

  • end_date : STRING : end date of the range. This date must be in "yyyy-MM-ddThh:mm:ssZ" format (iso format).

  • aggregation : STRING : can contain the following values :

    • MIN : minimum value

    • MAX : maximum value

    • SUM : sum

    • AVG : average

    • COUNT : number of values

    • none : no aggregation

  • [interval] : STRING : grouping to be applied to the aggregation. The possible values are in number of days (d), hours (h), minutes (m), seconds (s). Example 1d will return a value per day. This parameter is optional, it is only necessary if an aggregation is defined

  • [displayInRow]: BOOLEEN: Allows you to define whether the values will be displayed in rows or columns. The possible values are :

    • TRUE: display values in column

    • FALSE: display values in line

    • By default, the values will be displayed in column.

  • [displayTimestamp]: BOOLEEN: Allows you to define whether the timestamp should be displayed before the values. The possible values are :

    • TRUE: display the timestamp in the first row (or column as the case may be)

    • FALSE: only the values are displayed

    • By default the timestamp will be displayed.

  • [displayCount]: BOOLEEN: Allows you to define whether the number of results returned should be displayed before the values. The possible values are :

    • TRUE: display the number of results before the values

    • FALSE: the number of results is not displayed

      By default, the number of results will not be displayed.

  • [displayMetric] : BOOLEEN : Allows to define if the name of the metric should be displayed at the top, before the values. The possible values are :

    • TRUE: display the metric name in the first line, before the results

    • FALSE: the metric name is not displayed

      By default, the name of the metric is displayed

  • [precision] : STRING : precision which will be applied for the calculation of the aggregation. Possible values are in number of days (d), hours (h), minutes (m), seconds (s).

    Example :

    • start date: 01/01/2023

    • end date: 31/03/2023

    • Aggregation: AVG

    • Frequency: 1d

    • Precision: 1h

    • The result of the query will be the average per day of the tag. By indicating a precision, we will force the query to make groups to improve the calculation precision. In this case, a first average calculation will be made every hour of the day. Then the average of all these averages will be done to obtain the value for the day. If in precision 1h had been entered, then the daily average would have been obtained by averaging all the hourly values.

      If the parameter is not specified, the same value as the interval will be entered.

Notes

  • If an aggregation is selected, but no frequency is specified, then the calculation will be done on all the values in the range. For example, COUNT between two dates with no frequency will return the number of values in the base between the start and end dates

  • if an aggregation is selected, but no value on one of the frequency groupings is found, then the value returned is 0.

  • for the RANGE function, it is only possible to call one metric at a time

Examples

  • =iobase.IDB_RANGE("prod"; B6; A3; B3; "AVG"; "1d"; FALSE; TRUE; FALSE; FALSE)

    With B6 = tag name

    A3 = 2023-01-01T00:01:00Z

    B3 = 2023-04-18T23:59:00Z

This formula will return the daily average value of the tag between 1 January 2023 and 18 April 2024. There will therefore be one value returned per day, displayed in columns. The first column will return the timestamp, and the second the value. The name of the tag and the number of results will not be displayed

  • To manage dynamic dates, it is possible to use Excel formulas. For example, a cell can contain the current date at 23:59, and refresh automatically with the following formula: =CONCAT(YEAR(NOW());"-";RIGHT(CONCATENER("0";MONTH(NOW()));2);"-";RIGHT(CONCATENER("0";DAY(NOW()));2); "T23:59:00Z")

    The IDB_RANGE formula can then be based on this cell, which refreshes itself.

IDB_LATEST formula

Signature of the formula

=iobase.IDB_LATEST(datasource;metric;start_date;end_date;aggregation;[interval];[displayInRow];[displayTimestamp];[displayCount];[displayMetric];[precision])

The last 3 parameters are optional. If they are not filled in, the default values are described below.

Details of the parameters

  • datasource : STRING : name of the database (in general, the production data are in the prod database)

  • metric : STRING : name of the metric

  • [displayInRow] : BOOLEEN : Allows you to define whether the values will be displayed in rows or columns. The possible values are :

    • TRUE: display values in column

    • FALSE: display values in line

      By default, the values will be displayed in column.

  • [displayTimestamp]: BOOLEEN: Allows you to define whether the timestamp should be displayed before the values. The possible values are :

    • TRUE: display the timestamp in the first row (or column as the case may be)

    • FALSE: only the values are displayed

      By default the timestamp will be displayed.

  • [displayMetric] : BOOLEEN : Allows to define if the name of the metric should be displayed at the top, before the values. The possible values are :

    • TRUE: display the metric name in the first line, before the results

    • FALSE: the metric name is not displayed

      By default, the metric name is displayed

Notes

  • for the LATEST function, it is possible to call several metrics at the same time. To do this, separate the names of the metrics with ",".

  • if several metrics are selected, they must all be part of the same Source (same database). Otherwise, separate queries should be made.

Examples

  • =iobase.IDB_LATEST("prod"; "tag1,tag2"; TRUE; TRUE)

    This formula will return the last value in base for the tag1 and tag2 metrics. There will therefore be one value returned per metric, displayed in a row. The first line will return the timestamp, and the second the value. The name of the tag will be displayed.

Function IDB_INSTANT

This function retrieves the value of one or more metrics recorded at a specific timestamp.

Function Signature

=iobase.IDB_INSTANT(datasource;metric;date;[displayInRow];[displayTimestamp];[displayMetric])

The last 3 parameters are optional. If they are not provided, the default values described below are used.

Parameter Details :

  • datasource : STRING : database name (typically, production data is found in the 'prod' database)

  • metric : STRING : metric name

  • date : STRING : the reference timestamp in ISO format (e.g., "2025-04-14T08:00:38Z").

  • [displayInRow] : BOOLEAN : Defines whether values will be displayed in rows or columns. Possible values are:

    • TRUE : display values in columns

    • FALSE : display values in rows

    • By default, values will be displayed in columns.

  • [displayTimestamp] : BOOLEAN : Defines whether the timestamp should be displayed before the values. Possible values are:

    • TRUE : display the timestamp in the first row (or column, as applicable)

    • FALSE : only values are displayed

    • By default, the timestamp will be displayed.

  • [displayMetric] : BOOLEAN : Defines whether the metric name should be displayed at the top, before the values. Possible values are:

    • TRUE : display the metric name in the first line, before the results

    • FALSE : the metric name is not displayed

    • By default, the metric name is displayed.

  • [IsLocalDate] : BOOLEAN :

    • TRUE : display the timestamp in local format

    • FALSE : display the timestamp in ISO format

Notes :

  • For the INSTANT function, it is possible to call multiple metrics at once. To do this, separate the metric names with commas ",".

  • If multiple metrics are selected, they must all belong to the same Source (same database). Otherwise, separate queries will be necessary.

  • If no value exists at the exact selected timestamp, the query will return no value.

Example :

=iobase.IDB_INSTANT("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:38Z"; FAUX; VRAI; VRAI; FAUX)

This formula will return the value from the database at the selected timestamp 2025-04-14T08:00:38Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric :

Function IDB_INSTANT_BEFORE

This function retrieves the value of one or more metrics recorded just before a specific timestamp.

Function Signature:

=iobase.IDB_INSTANT_BEFORE(datasource;metric;date;[displayInRow];[displayTimestamp];[displayMetric])

The last 3 parameters are optional. If they are not provided, the default values are applied (display in columns, with timestamp and metric name).

Parameter Details

  • datasource : STRING : database name (e.g., "prod").

  • metric : STRING : metric name or names separated by commas (e.g., "tag1,tag2").

  • date : STRING : the reference timestamp in ISO format (e.g., "2023-04-11T14:45:43Z").

  • [rangeLimiter] : STRING : Allows defining a maximum time window for searching the previous value. The function will search for the last recorded value before the chosen date, but will stop its search if it needs to go back further than the duration specified in "rangeLimiter". The expected value is a duration (e.g., "1d", "2h", "30m", "10s" for day, hour, minute, second).

  • [displayInRow] : BOOLEAN :

    • TRUE : display values in columns (default).

    • FALSE : display values in rows.

  • [displayTimestamp] : BOOLEAN :

    • TRUE : display the exact timestamp of the found value (default).

    • FALSE : only values are displayed.

  • [displayMetric] : BOOLEAN :

    • TRUE : display the metric name (default).

    • FALSE : the metric name is not displayed.

  • [IsLocalDate] : BOOLEAN :

    • TRUE : display the timestamp in local format

    • FALSE : display the timestamp in ISO format

Notes :

  • It is possible to call multiple metrics simultaneously by separating them with commas.

  • If multiple metrics are selected, they must belong to the same datasource. Otherwise, separate queries are necessary.

  • If no value exists exactly at the specified date, the function will return the last recorded value strictly before this timestamp, constrained by the rangeLimiter if provided.

Example:

=iobase.IDB_INSTANT_BEFORE("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:10Z"; "1d"; FAUX; VRAI; VRAI; FAUX)

This formula will return the value recorded in the database just before the selected timestamp 2025-04-14T08:00:10Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric:

Function IDB_INSTANT_AFTER

This function retrieves the value of one or more metrics recorded just after a specific timestamp.

Function Signature

=iobase.IDB_INSTANT_AFTER(datasource;metric;date;[rangeLimiter];[displayInRow];[displayTimestamp];[displayMetric];[IsLocalDate])

The last 3 parameters are optional. If they are not provided, the default values are applied (display in columns, with timestamp and metric name).

Parameter Details :

  • datasource : STRING : database name (e.g., "prod").

  • metric : STRING : metric name or names separated by commas (e.g., "tagA,tagB").

  • date : STRING : the reference timestamp in ISO format (e.g., "2023-11-20T08:00:00Z").

  • [rangeLimiter] : STRING : Allows defining a maximum time window for searching the next value. The function will search for the first recorded value after the chosen date, but will stop its search if it needs to go further forward than the duration specified in "rangeLimiter". The expected value is a duration (e.g., "1d", "2h", "30m", "10s" for day, hour, minute, second).

  • [displayInRow] : BOOLEAN :

    • TRUE : display values in columns (default).

    • FALSE : display values in rows.

  • [displayTimestamp] : BOOLEAN :

    • TRUE : display the exact timestamp of the found value (default).

    • FALSE : only values are displayed.

  • [displayMetric] : BOOLEAN :

    • TRUE : display the metric name (default).

    • FALSE : the metric name is not displayed.

  • [IsLocalDate] : BOOLEAN :

    • TRUE : display the timestamp in local format

    • FALSE : display the timestamp in ISO format

Notes:

  • It is possible to call multiple metrics simultaneously by separating them with commas.

  • If multiple metrics are selected, they must belong to the same datasource. Otherwise, separate queries are necessary.

  • If no value exists exactly at the specified date, the function will return the first recorded value strictly after this timestamp, constrained by the rangeLimiter if provided.

Example:

=iobase.IDB_INSTANT_AFTER("main"; "nj26uv_temperature_maternelle,nj26uv_temperature_garderie"; "2025-04-14T08:00:10Z"; "1d"; FAUX; VRAI; VRAI; FAUX)

This formula will return the value recorded in the database just after the selected timestamp 2025-04-14T08:00:10Z for the metrics nj26uv_temperature_maternelle and nj26uv_temperature_garderie. There will therefore be one value returned per metric :

IDB_DATE Formula

This formula returns a date formatted to the ISO standard.

Formula Signature : =iobase.IDB_DATE([date])

[date] parameter :

  • optional: If this parameter is omitted, the function returns the current date and time.

  • expected type: The [date] parameter must be a Date object, not a simple text string.

Examples :

=iobase.IDB_DATE(TODAY()+4) : Returns today's date + 4 days.

=iobase.IDB_DATE(NOW()) : Returns the current date.

Notes :

  • You can convert a text string into a proper Date object using the excel DATEVALUE function.

    For example : =iobase.IDB_DATE(DATEVALUE("12/25/2025"))

  • The IDB_Date function can be used within other IO-base functions.

Did this answer your question?