Skip to main content
All CollectionsIndaSheetIndaSheet Excel
Data recovery - Formulas
Data recovery - Formulas
Updated over 2 months ago

Pre-requisite: 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.

IDB_INSTANT formula

Signature of the formula

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

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 the 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 INSTANT 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 will have to be made

  • if no value exists at the exact timestamp selected, the query will retrieve the last value recorded in the database just before the selected timestamp

Examples

  • =iobase.IDB_INSTANT("prod"; "tag1,tag2"; "2023-04-11T14:45:43Z";FAUX;VRAI;VRAI)

    This formula will return the value in base just before the timestamp 2023-04-11T14:45:43Z for the tag1 and tag2 metrics. There will therefore be one value returned per metric, displayed in columns. The first column will show the name of the tag, and the second the value. The number of results will not be displayed.

IDB_DATE Formula

Signature of the formula

=iobase.IDB_DATE([date])

The parameter is optional. If ever it is not filled, the formula will return the current date and time in the format here.

Detail of the parameters

  • [date]: DATE: the date you wish to have in ISO format, for use in other io-base functions.

Examples

  • =iobase.IDB_DATE(TODAY()+4)

Returns the current date + 4d

Did this answer your question?