Data Analysis and Reporting with Intelligence Clients

Using Weighted Functions
Weighted averages can be very useful for a variety of purposes, such as survey research or when you want to include demographic information in your pivot tables. For example, assume you took a survey of 100 people, 75 male and 25 female. But according to census data in that geographic region you should have surveyed 50 males and 50 females. The data you have is skewed toward males.
To correct for this, you assign a weight or weighting factor to correct for the sampling error in your survey. To calculate a weight you take the expected amount and divide it by the actual amount.
In the example, the men would have a weighting factor of:
The women would have a weight of:
Any calculation would calculate each man as 0.6666 and each woman as 2.
Weighted averages can also be used to apply different levels of importance to a given item. Take, for example, a survey, which has multiple questions. The responses can be rated on a scale of 1 to 5. By assigning a weight to each question based on the level of importance (the higher the number the more important), and using that weight in calculating a weighted average, you can arrive at averages that are more meaningful.
To use weighted averages, you must add a column of data to the database. This data indicates the relative weight of each corresponding value in another column. The statistical calculation for weighted averages depends on the following mathematical formula:
Weighted Values Example
Customer ID Units Sold Type of Store Weight 1435 80 Electronics 8 1539 200 Computer 10 1634 60 Electronics 8 1213 900 Discount 2
To use weighted averages:
 Ensure that a column of data with the weighted values exists in the database.
 In the Query section, select the Topic item for which weighted values are needed.
 Choose QueryData FunctionsWeight.
The Reference dialog box appears.
 Select the item that contains the weighted values and click OK.
The item in the Request line is renamed to indicate it is a weighted value.
 Process the query.
The weighted values are returned in the Results section.
 Go to the Pivot section and drag the Weighted item from the Catalog pane to the Outliner.
You may now use all of the various data functions on the weighted values.
