Data-based queries, Filter, Sum, Avg and Stdev

Sometimes it is not adequate to simply use a single value in a query; you need to consider a combination of values. It might be that this combination is only needed for one or two queries, though, so it is not desirable to calculate and store the result in the cube for all to see. Therefore it is more logical to quickly calculate the result on the fly and although this is then repeated every time the subset is used, it is still the preferred choice. The function Sum, Avg and Stdev are therefore useful for things that are only needed occasionally or by a limited number of users and means that the actual cube is thus smaller and more efficient.

SUM, as it might appear, sums up a set of numbers. This allow the aggregation of members not already consolidated in the model. This example checks the Test3 cube for products whose Amounts in the on-the-fly-consolidation of 2 entities are greater than 50.
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
SUM(
{[Entity].[MidasLDN], [Entity].[Entity Not Applicable]},
Test3.([Posting Measures].[Amount]) ) > 50
)}

AVG calculates the average value of a set. Note how empty (zero) cells are not included by the AVG function so the resulting average value might be higher than you expected.

This example returns a list of leaf products that have an Amount value in the Test cube higher than the average Amount value of all leaf products (or rather all non-zero leaf products).
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
(Test.[Posting Measures].[Amount] >
AVG({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]))
)
)}

The set of members that AVG works on here (AVG{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}) can be changed to something that doesn’t match the list of members being filtered earlier in the query. For example, return a list of all leaf products that are higher than the average of the leaf descendants of the Customer Lending consolidation only.
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
(Test.[Posting Measures].[Amount] >
AVG({DESCENDANTS(Product.[Customer Lending]) },[Test].([Posting Measures].[Amount])))
)}

STDEV is the standard deviation function. It returns the average distance from each value in a set to the average of the set as a whole. In this way you can calculate how consistent or unpredictable a set of data is – if all the values lie tightly around the average, or if the values vary to be extremely high and low.

This example returns the outlying products whose Amount value in the Test cube is greater than the average value plus the standard deviation – i.e. those products who have values that are “above averagely above the average”.
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
(
Test.[Posting Measures].[Amount] >
(
AVG({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]))
+
STDEV( {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 0 )}
)
)
)
)}

Note that the AVG function automatically drops empty cells from the filtering set but STDEV does not so we have to apply our own filter.

The equivalent lower-bound outlier query would be:
{FILTER(
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 0 )},
(
Test.[Posting Measures].[Amount] 0 )}
)
)
)
)}

The above queries could be INTERSECTed for both sets of outliers in one subset, if required.

Further reading: The MEDIAN function is also supported by TM1 and might be more appropriate than AVG (mean) in some circumstances.