Except
The MDX Except function for Essbase returns a subset containing the differences between two sets, optionally retaining duplicates. The two input sets must have identical dimensionality.
Syntax
Except ( set1, set2 [,ALL] )
Parameters
Example
Except( {[New York], [California], [Florida], [California]},
{[Oregon], [Washington], [California], [Florida]})
returns {[New York]}
.
Except( {[New York], [California], [Florida], [California]},
{[Oregon], [Washington], [California], [Florida]}, ALL)
returns {[New York], [California]}
.
The following query returns Actual Sales and Profit numbers for the level-0 markets that are not defined as "Major Market."
SELECT
{[Measures].[Sales], [Measures].[Profit]}
ON COLUMNS,
Except(
[Market].Levels(0).Members,
UDA (Market, "Major Market")
) ON ROWS
FROM Sample.Basic
WHERE {([Year].[Qtr1], [Scenario].[Actual])}
This query returns the grid:
Table 4-61 Output Grid from MDX Example
(axis) | Sales | Profit |
---|---|---|
Connecticut | 3472 | 920 |
New Hampshire | 1652 | 202 |
Oregon | 5058 | 1277 |
Washington | 4835 | 1212 |
Utah | 4209 | 744 |
Nevada | 6516 | 775 |
Oklahoma | 2961 | 718 |
Louisiana | 2906 | 773 |
New Mexico | 1741 | 4 |
Wisconsin | 4073 | 913 |
Missouri | 3062 | 399 |
Iowa | 6175 | 2036 |