HP Storage Essentials Storage Resource Management Report Optimizer Software 6.0 Performing On-Report Analysis with Web Intelligence (August 2008)

Performing On-Report Analysis with Web Intelligence 225
• calculate the difference between each number of items sold and the average, then
square this value
• add up all these squared differences
• divide this total by the number of values - 1
Without the use of variables to simplify it, this formula is as follows:
Sum((([Quantity sold] - Average([Quantity sold] ForEach [Quarter]) In Report)*([Quantity
sold] - Average([Quantity sold] ForEach [Quarter]) In Report)) In [Quarter])/(Count
([Quantity sold] ForEach [Quarter]) - 1)
This formula is clearly unwieldy. By using variables you can simplify it to:
Sum ([Difference Squared])/[Number of Observations] - 1)
which is much easier to understand. This simplified version of the formula gives you a
high-level view of what the formula is doing, rather than plunging you into the confusing
details. You can then examine the formulas of the variables referenced in the high-level
formula to understand its component parts.
For example, the formula references the variable Difference Squared, which itself
references the variable Average Sold. By examining the formulas of Difference Squared
and Average sold, you can drill down into the formula to understand the details of what it
is doing.
Simplifying a variance formula with variables
As the example above shows, there are several steps involved in creating a variance
formula. You encapsulate each of these steps in a variable. The variables you create are:
• average number of items sold
• number of observations (that is, the number of separate values of the number of items
sold)
• difference between an observation and the average, squared
• sum of these differences divided by the number of observations - 1
In this example, your initial report looks like this:
Creating an Average Sold variable
Quarter Quantity sold
Q1 61,808
Q2 54,406
Q3 55,690
Q4 51,325