You can combine two sets to compare the members. When you combine sets you create a new set containing either the combination of all members, just the members that exist in both, or members that exist in one set but not the other. Combining sets allows you to answer complex questions to understand cohorts of data. For example, to determine the percentage of customers who purchased both last year and this year, you can combine two sets containing the customers from each year and return only the customers that exist in both sets. Another example would be to determine what products sell the most but also have the highest return rate.
To combine two sets, they must be based on the same dimensions. That is, you can combine a set containing the top customers with another set containing the customers that purchased last year. However, you cannot combine the top customers set with a top products set. In the latter case, the one set is based on the Customer dimension while the other is based on the Product dimension.
Select two sets in the Data window that you want to combine.
Right-click the sets and select Create Combined Set.
In the Create Set dialog box, type a name for the new combined set.
Verify that the two sets you want to combine are selected in the two drop-down menus.
Select one of the following options for how to combine the sets:
All Members in Both Sets - the combined set will contain all of the members from both sets.
Shared Members in Both Sets - the combined set will only contain members that exist in both sets.
Except Shared Members
- the combined set will contain all members from the specified set that
don't exist in the second set. These options are equivalent to
subtracting one set from another. For example, if the first set contains
Apples, Oranges, and Pears and the second set contains Pears and Nuts;
combining the first set except the shared members would contain just
Apples and Oranges. Pears is removed because it exists in the second
- All Members in Both Sets - the combined set will contain all of the members from both sets.
Optionally specify a character that will separate the members if the sets represent multiple dimensions.
When finished, click OK.
Note: This functionality is not available in workbooks created before version 8.2 that use Microsoft Excel or text file data sources, workbooks that use the legacy connection, or workbooks that use Microsoft Access data sources.