These are special XLReporting functions that you can use in models to pull reasltime data into cells.
For further information on models and queries, please refer to Define models.
Returns the average value from a column in the given data query, for all rows where the key column matches the "data" value.
DAVERAGE(data, query, column, key)
Example:
DAVERAGE("2017", 1)
Inserts dynamic values from a column in the given data query into a range of subsequent columns in a model, starting at the current cell. The "type" parameter determines whether a new column will be inserted for every value.
This only takes effect when the model is being generated in Run model. In Define model, the cell that contains this function only shows information.
DCOLUMNS is similar to DROWS, each populating the query data into a different direction in the model. If you want to insert cell ranges or results from other functions into columns (as opposed to query data), you should use the TRANSPOSE function.
Please note that you can not combine this function with other functions in the same cell.
DCOLUMNS(query, column, type)
Type:
0 - overwrite cells in existing columns (default)
1 -
insert new column for every value
Example:
DCOLUMNS(1, 1)
Returns the number of rows in the given data query, where the key column matches the "data" value.
DCOUNT(data, query, column, key)
Example:
DCOUNT("2017", 1)
Returns the number of non-empty and non-zero values in a column in the given data query, for all rows where the key column matches the "data" value.
DCOUNT(data, query, column, key)
Example:
DCOUNT("2017", 1)
Returns a single value from a column in the given data query, from the first row where the key column matches the "data" value.
DGET(data, query, column, key)
Example:
DGET("2017", 1)
Returns a single value from a column on the given row number in the given data query. The "row" parameter must be an integer between 1 and the number of rows in the query data.
DINDEX(query, row, column)
Example:
DINDEX(2, 1)
Returns the highest value from a column in the given data query, for all rows where the key column matches the "data" value.
DMAX(data, query, column, key)
Example:
DMAX("2017", 1)
Returns the lowest value from a column in the given data query, for all rows where the key column matches the "data" value.
DMIN(data, query, column, key)
Example:
DMIN("2017", 1)
Returns the data either from a single column or from all columns, in the given data query, for all rows where the key column matches the "data" value. The returned data has the same structure as a cell range or data array, which enables you to pass query data directly into other functions that expect a cell range or data array (for example: math functions, aggregate functions, or sparkline functions).
The "column" parameter must be an integer, either representing a single column starting at 1 (e.g. 3 for the 3rd column), or 0 for all columns.
DRANGE(data, query, column, key)
Examples:
MEDIAN(DRANGE("2017", 1))
COMPARE(A2:C2,
DRANGE("2017", 0))
Inserts dynamic values from a column in the given data query into a range of subsequent rows in a model, starting at the current cell. The "type" parameter determines whether a new row will be inserted for every value.
This only takes effect when the model is being generated in Run model. In Define model, the cell that contains this function only shows information.
DROWS is similar to DCOLUMNS, each populating the query data into a different direction in the model. If you want to insert cell ranges or results from other functions into rows (as opposed to query data), you should use the TRANSPOSE function.
Please note that you can not combine this function with other functions in the same cell.
DROWS(query, column, type)
Type:
0 - overwrite cells in existing rows (default)
1 -
insert new row for every value
Example:
DROWS(1, 1)
Returns the sum of all values from a column in the given data query, for all rows where the key column matches the "data" value.
DSUM(data, query, column, key)
Example:
DSUM("2017", 1)
Returns the query name and type of a data query. The "range" parameter is the cell range into which the query data will be inserted.
QUERY(query, range)
Example:
QUERY(1, A2:D5)