Worked Examples - Reference Items

This section contains some worked examples of reference items in use. Please see the introduction to field/axis reference items before following the examples.

Example Scenario

The examples use the following base table, which shows Gender and Occupation by Marital Status, using the fields from the sample Retail Banking database supplied with SuperSTAR. The fields have been recoded to hide some of the values and add totals.

There are a number of questions you might want to ask about this data. The following examples show how you can use derivations and field reference items to answer some typical questions.

Example 1 - What Percentage of Married Males are Self Employed?

To answer this question:

1. Make sure that the Occupation field has its Field Reference Item set as the Total (you can check this by right-clicking any of the Total rows under Occupation):
   

   

2. Right-click one of the Marital Status column headings and select Derivations > Add Field Derivation. The Define Derivation dialog displays.
3. Give the derivation a name and start defining the formula by adding the value of the Married field (V2 in this example) and the % operator:
   

   

4. Add the reference to the total number of married people:
   
   1. Click Add Reference.
   2. Click Field Reference Setting.
   3. Select the Occupation field (this selects the reference item total from the Occupation field).
   4. In the Define Reference dialog, select the Married value and click Add to Derivation.

   

   
   The final derivation should look something like this:
   

   

5. Click OK. The table updates with the new column, showing that 29% of married males are self employed:
   

   

Example 2 - What Percentage of Self Employed Married People are Male?

To answer this question.

1. Make sure that the Gender field has its Field Reference Item set as the Total (you can check this by right-clicking the Total row):
   

   

2. Define the derivation in the same way as the first example, but this time make sure the Field Reference Setting is based on the reference item for Gender (because this contains the combined totals for both genders for each occupation):
   

   

   
   The derivation should look something like this:
   

   

3. Click OK. The table updates with the new column, showing that 74% of self employed married people are male:
   

   

Example 3 - What Percentage of Married People are Self Employed Males?

To answer this question:

1. Define the derivation as above, but this time make sure the Field Reference Setting is based on the combination of the reference items for Gender and Occupation. This will give us a percentage of the total that intersects both reference items:
   

   

2. The table will appear as follows:
   

   

Example 4 - What Percentage of People Doing Each Occupation are Married?

There are two options for answering this question:

• Define a derivation with the field reference based on the Total value of the Marital Status field:
  

  

• Or simply set the formula to calculate the percentage:
  

  

Both options give the same results in the table: