Examples of Sets

There are many ways you can use sets to answer complex questions and compare cohorts of data. Below are some examples of ways you can use sets to define and compare subsets of data.

How do members of a set contribute to the total?

You may have all kinds of questions surrounding how the members in a set contribute to the overall total. For example, what percent of total sales come from repeat customers? You can answer these types of questions using the IN/OUT mode for set. The example below uses sales data to calculate the percent of total sales from customers who have purchased at least 20 times.

1. Right-click the Customer Name dimension in the Data window and select Create Set.
   
   
2. In the Create Set dialog box, type a name for the set. In this example, we'll call the set, "Frequent Customers"
   
3. Select the Use All option so the condition always applies to all values even when new customers are added.
   
   
4. On the Condition tab, select By Field and define a condition that only includes customers when Count (Distinct) of Order ID is greater than or equal to 20.
   
   
5. When finished, click OK.
   
6. Drag the new set from the Sets area at the bottom of the Data window to the Rows shelf.
   
7. Drag the Sales measure to the Columns shelf. The view now shows the total sales for frequent customers and total sales for all other customers.
   
   
8. Finally, on the field menu for Sales, select Quick Table Calculation > Percent of Total.
   
   

The view now shows that frequent customers make up about 39% of the overall sales.

How many members of a set exist in another set?

Another common use of sets is to compare subsets of data or cohorts. For example, you may wonder how many customers that purchased last year also purchased this year. Or if a customer purchased a specific product, what other products did they buy? You can answer these types of questions by creating multiple sets and combining them. The example below uses sales data to determine how many customers that purchased in 2010 also purchased in 2011.

1. Drag the Customer Name field to the Rows shelf.
   
2. Drag the Order Date field to the Filters shelf.
   
3. In the Filter Field dialog box, select Years and click Next.
   
   
4. In the Filter dialog box, select 2010 and click OK.
   
   
5. Back in the view, press CTRL + A on your keyboard to select all of the customers.
6. Right-click the selection and select Create Set.
   
   
7. In the Create Set dialog box, type a name for the set. In this example, we'll call the set "Customers (2010)".
   
   
8. When finished, click OK.
   
9. On the field menu for the Order Date field on the Filters shelf, select Filter.
   
   
10. Change the filter to only include 2011 instead of 2010. Then click OK.
    
    
11. Again, press CTRL + A on your keyboard to select all of the customers.
12. Right-click the selection and select Create Set.
    
13. In the Create set dialog box, type a name for the set. This set will be called "Customers (2011)".
    
    
14. When finished, click OK.
    
15. Hold the CTRL key on your keyboard to select both the Customers (2010) set and the Customers (2011) set in the Data window.
16. Right-click the selection and select Create Combined Set.
    
    
17. In the Create Set dialog box, type a name for the new set. In this example, we'll call the set "Customers (2010 & 2011)".
    
18. Make sure the correct two sets are selected in the drop-down menus.
    
19. Select the option to include Shared Members in Both Sets.
    
    
20. When finished, click OK.
    
21. Create a new sheet by clicking the New Worksheet tab at the bottom of the workbook.
    
    
22. Drag Customer Name to the Rows shelf.
    
23. On the Customer Name field menu, select Measure > Count (Distinct).
    
24. Finally, drag the Customers (2010 & 2011) field to the Filters shelf. You can see that 511 customers who purchased in both 2010 and 2011.
    
    

Hierarchical Sets and Descendants

A hierarchical set filters data to the selected members and all of their descendants. For example, a set named Dairy is created from the Product hierarchy. As shown below, it includes only the Dairy product department.

Consider the following view. The Product Category dimension is placed on the Rows shelf and the Store Sales measure is placed on the Columns shelf.

If you place the Dairy set on the Filters shelf, you can see that the view is filtered to include only the Dairy product categories.

As shown below, you can drill down into Product Department to reveal the Product Category, Product Subcategory, and Brand Name levels. As these descendants are revealed, row headers are added to the view. This is because a set filter allows you to view the levels of detail contained within the filtered members.