In this article:
All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

Functions for queries (models only)

These are special XLReporting functions that you can use in models to pull reasltime data into cells.

✭ Tips:

Most query functions use these common parameters:

For further information on models and queries, please refer to Define models.

DAVERAGE

Returns the average value from a column in the given query, for all rows where the key column matches the data parameter.

DAVERAGE(data, query, column, key)

Example:
DAVERAGE("2019", 1) = 5

DCOLUMNS ❖ XLReporting

Inserts dynamic values from a column in the given 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)

DCOUNT

Returns the number of rows in the given query, where the key column matches the data parameter.
If you just want to know the total number of rows in a query, you can also use DSIZE(query)

DCOUNT(data, query, key)

Example:
DCOUNT("2019", 1) = 10

DCOUNTA

Returns the number of non-empty and non-zero values in a column in the given query, for all rows where the key column matches the data parameter.

DCOUNTA(data, query, column, key)

Example:
DCOUNTA("2019", 1) = 6

DGET ❖ XLReporting

Returns a single value from a column in the given query, from the first row where the key column matches the data parameter.

DGET(data, query, column, key)

Example:
DGET("2019", 1) = "4000"

DINDEX ❖ XLReporting

Returns a single value from a column on the given row number in the given 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) = 50

DMAX

Returns the highest value from a column in the given query, for all rows where the key column matches the data parameter.

DMAX(data, query, column, key)

Example:
DMAX("2019", 1) = 200

DMIN

Returns the lowest value from a column in the given query, for all rows where the key column matches the data parameter.

DMIN(data, query, column, key)

Example:
DMIN("2019", 1) = 100

DRANGE ❖ XLReporting

Returns the data from either a single column or multiple columns, in the given query, for all rows where the key column matches the data parameter. 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 the column number or name to retrieve a single column (e.g. 3 for the 3rd column), or 0 to retrieve all columns, or -1 to retrieve all columns after the key column.

DRANGE(data, query, column, key)

Examples:
MEDIAN(DRANGE("2019", 1))
COMPARE(A2:C2, DRANGE("2019", 0))

DROWS ❖ XLReporting

Inserts dynamic values from a column in the given 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)

DSIZE ❖ XLReporting

Returns the total number of rows in the given query.

DSIZE(query)

Example:
DSIZE(1) = 10

DSUM ❖ XLReporting

Returns the sum of all values from a column in the given query, for all rows where the key column matches the data parameter.

DSUM(data, query, column, key)

Example:
DSUM("2019", 1) = 200

QUERY ❖ XLReporting

Returns the query name and type of a given query. The range parameter is the cell range into which the query data will be inserted.

QUERY(query, range)

Example:
QUERY(1, A2:D5)

Recommended reading:
Back to top | Models | Reports

We value your privacy

We use cookies to enhance your browsing experience and analyze our traffic.
By accepting, you consent to our use of cookies.

Accept Reject Cookie Policy