Returns a subset containing the differences between two sets, optionally retaining duplicates. The two input sets must have identical dimensionality.
Except ( set1, set2 [,ALL] )
| set1 | A set to compare with set2. |
| set2 | A set to comparet with set1. |
| ALL | The optional ALL flag retains duplicates. Matching duplicates in set1 and set2 are eliminated. |
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
WHERE {([Year].[Qtr1], [Scenario].[Actual])}
This query returns the grid:
| 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 |
| ©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |