MIKE Workbench Functions¶
In addition to the standard spreadsheet functions (also known from Excel), such as SUM, AVG, COUNT, IF, etc., the MIKE Workbench provides some functions to access the data in the MIKE Workbench database directly from the spreadsheet.
The functions are listed below with their name, arguments (mandatory and/or optional) and description of what they do.
Common to them all is that they are reached through typing in the equal sign (‘=’) in a cell followed by the name. The list of functions show up filtered by the typed in text.
Selecting a function from the drop-down list with the mouse will start a tooltip display of the arguments as they are entered.
Finalizing the function call with a parenthesis and typing enter evaluates the function.
GetCellValueByRC¶
| Syntax | GetCellValueByRC(path, sheet, row, col) Gets the cell value in a sheet in the workbook by row and column number |
|---|---|
| Arguments | Path: string, required – the path in explorer to the spreadsheet starting with a “/” Sheet: string, required – the name of the sheet within the workbook Row: integer, required – the row number of the cell Col: integer, required – the column number of the cell |
| Example | GetCellValueByRC(“/MyGroup/MyFirstSheet”, “Sheet1”, 2, 3) |
GetCellValueByRef¶
| Syntax | GetCellValueByRef (path, sheet, reference) Gets the cell value in a sheet in the workbook by cell reference |
|---|---|
| Arguments | Path: string, required – the path in explorer to the spreadsheet starting with a “/” Sheet: string, required – the name of the sheet within the workbook Reference: string, required – the celle reference |
| Example | GetCellValueByRC(“/MyGroup/MyFirstSheet”, “Sheet1”, “C3”) |
GetRangeValueByRC¶
| Syntax | GetRangeValueByRC(path, sheet, row1, col1, row2, col2) Gets the cell value in a sheet in the workbook by row and column numbers |
|---|---|
| Arguments | Path: string, required – the path in explorer to the spreadsheet starting with a “/” Sheet: string, required – the name of the sheet within the workbook Row1: integer, required – the row number of the upper left cell Col1: integer, required – the column number of the upper left cell Row2: integer, required – the row number of the lower right cell Col2: integer, required – the column number of the lower right cell |
| Example | GetRangeValueByRC (“/MyGroup/MyFirstSheet”, “Sheet1”, 2, 3, 4.5) |
GetRangeValueByRef¶
| Syntax | GetRangeValueByRef (path, sheet, reference) Gets the cell value in a sheet in the workbook by range reference |
|---|---|
| Arguments | Path: string, required – the path in explorer to the spreadsheet starting with a “/” Sheet: string, required – the name of the sheet within the workbook Reference: string, required – the range reference |
| Example | GetCellValueByRC(“/MyGroup/MyFirstSheet”, “Sheet1”, “C3:F4”) |
GetTimeseries¶
| Syntax | GetTimeseries (path,![starttime],[endtime]) Gets the time series by path and optionally subset by date time |
|---|---|
| Arguments | Path: string, required – the path in explorer to the timeseries starting with a “/” starttime: string, optional – start date and time of interval to select, in format “yyyy-MM-dd HH:mm:ss” endtime: string, optional – end date and time of interval to select, in format “yyyy-MM-dd HH:mm:ss” |
| Example | GetTimeseries("/group/waterlevel") GetTimeseries("/group/waterlevel", “2010-01-01”, 2010-03:31 23:59:59”) |
GetTimeseriesQuantile¶
| Syntax | GetTimeseriesQuantile (path, fraction) Gets the time series quantile by path and fraction |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” Fraction: float, required – number between 0 and 1 |
| Example | GetTimeseries("/group/waterlevel", 0.50) |
MovingAverageTimeseries¶
| Syntax | MovingAverageTimeseries(path, Days,![Hours],![Minutes],![seconds],![AveragingWindowPosition],![InterpolateAcrossGaps],![MaxNumberOfMissingValuesPerGap]) Gets a time series calculated as moving average of the source |
|---|---|
| Arguments | Path: string, required – the path in explorer to the timeseries starting with a “/” days: integer, required – number of days to average hours: integer, optional – hours added to number of days to average minutes: integer, optional – minutes added to number of days to average seconds: integer, optional – seconds added to number of days to average AveragingWindowPosition: integer, optional – start number of day to average InterpolateAcrossGaps: boolean, optional– true/false MaxNumberOfMissingValuesPerGap: integer, optional – number of values to accept as a gap |
| Example | GetTimeseries("/group/waterlevel") GetTimeseries("/group/waterlevel", “2010-01-01”, 2010-03:31 23:59:59”) |
TimeseriesMaximumValue¶
| Syntax | TimeseriesMaximumValue(path) Gets the maximum value in a time series identified by by path |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| Example | TimeseriesMaximumValue("/group/waterlevel") |
TimeseriesMinimumValue¶
| Syntax | TimeseriesMinimumValue (path) Gets the minimum value in a time series identified by by path |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| Example | TimeseriesMinimumValue("/group/waterlevel") |
TimeseriesAnnualMaximum¶
| Syntax | TimeseriesAnnualMaximum(path,![startday],![startmonth]) Gets the maximum value within a year value in a time series identified by by path. The year starts 1. January unless start day and start month is specified |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| startday: integer, optional – day within a month. Default = 1 | |
| startmonth: integer, optional – number of a month (1-12). Default = 1 | |
| Example | TimeseriesMinimumValue("/group/waterlevel", 29, 2) |
TimeseriesAnnualMinimum¶
| Syntax | TimeseriesAnnualMinimum(path,![startday],![startmonth]) Gets the minimum value within a year value in a time series identified by by path. The year starts 1. January unless start day and start month is specified |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| startday: integer, optional – day within a month. Default = 1 | |
| startmonth: integer, optional – number of a month (1-12). Default = 1 | |
| Example | TimeseriesMinimumValue("/group/waterlevel", 29, 2) |
TimeseriesExceedence¶
| Syntax | TimeseriesExceedence (path, threshold) Calculates the volume of a curve above the specified threshold |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| threshold: float, required– threshold value to compare | |
| Example | TimeseriesExceedence ("/group/waterlevel", 154) |
TimeWeighedAverageValue¶
| Syntax | TimeWeighedAverageValue (path) Calculates the time weighed average of a time series |
|---|---|
| Arguments | Path: string, required – the path in explorer to the time series starting with a “/” |
| Example | TimeWeighedAverageValue ("/group/waterlevel") |