STAT Function

STAT generates aggregated statistics based on another result set:

STAT([aggregation], index_of_column_to_aggregate, optional_parameter, optional_parameter... , BQL_query)

Allowable aggregations include: MEDIAN, AVG, MIN, MAX, SUM, COUNT, COUNTDISTINCT, and STDDEV.

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

Reference each parameter expression in the lookup query using %index syntax. The first parameter is numbered %0, the second %1, etc.

Important: Parameters require the lookup query to be issued for each row in the parent query and can affect performance. Use the display filter DISPLAY WHERE syntax to run these queries against the returned dataset in cache for improved performance. See Display Operations.

Tip: STAT can return a value from a data set that doesn't join with the data in the report. For example, the grayed-out attributes or measures in Designer or Visualizer that don't join with what is already on the report, can be accessed with STAT.

Note: STAT queries in a pushdown enabled space now return NULL instead of Zero.

Example STAT Function

For example, to show how many products sold for less than their cost:

STAT(COUNT, 0, SELECT [Products.Products], [Order_Date: Sum: Unit_Price] FROM [ALL] WHERE [Order_Date: Sum: Unit_Price] - [Order_Date: Sum: Unit_Cost] <= 0)

To show that in context of Categories and Revenue:

SELECT TOP 100 USING OUTER JOIN STAT(COUNT, 0, SELECT [Products.Products], [Order_Date: Sum: Unit_Price] FROM [ALL] WHERE [Order_Date: Sum: Unit_Price] - [Order_Date: Sum: Unit_Cost] <= 0) 'COL0' , [Order_Date: Sum: Revenue] 'COL1' , [Products.Categories] 'COL2' FROM [ALL]

Naturally, the business user would want the answer to be zero.

Example STAT Function Using a Lookup Parameter

Example highlighting the parameter syntax in a STAT lookup:

SELECT [Products.CategoryName], STAT(Sum, 1,[Order_Date: Sum: Quantity], SELECT [Products.CategoryName],[Order_Date: Sum: Quantity] FROM [ALL] DISPLAY WHERE [Order_Date: Sum: Quantity]>=%0)/[Order_Date: Sum: Quantity]{Products.Total}

Tip: Using parameters in this STAT expression supports a typical Pareto chart calculation. The subquery filters for all values that are at or above the value from the main query. In Wikipedia see Pareto chart.