Using Set operators to exclude values from results

Many people who start using Set Analysis will learn how to include values that they want in the results using modifiers. However, it is not always easy to work out how to exclude values. This recipe will show you how to do it.

Getting ready

Load the following script:

LOAD * INLINE [
  Country, Year, Sales
  USA, 2011, 4322
  USA, 2012, 5322
  USA, 2013, 6521
  UK, 2011, 1985
  UK, 2012, 2319
  UK, 2013, 3211
];

How to do it...

Perform the following steps to exclude the values from the results using Set operators:

1. Add a straight table with Country as dimension. Add the following expressions:

   Sales $	Sum(Sales)
   Sales ex. 2013	Sum({<Year=-{2013}>} Sales)
   Sales less 2013	Sum({<Year-={2013}>} Sales)

   The syntax highlight may suggest that these are incorrect; they are not!

2. Add a listbox for Year.

   

3. Notice the values with no selections on. Both of the values excluding 2013 appear to be the same. Select 2012 in the listbox:

   

4. Note...