Geometric Mean and Product Aggregate Function

This article defines MAQL to calculate Geometric Mean and Product Aggregate Function.

Background

The arithmetic mean is what most people think of when they think of the “average” value of a data set. It is calculated by summing all of the values in the data set and then dividing the sum by the number of values.

However, it is not always appropriate to use the arithmetic mean (MAQL Mean) to calculate an average. For example, suppose your stock broker told you that your investments averaged 25% growth over the past two years. This might sound like good news, until you learn that your stocks increased 100% and then lost 50% of their value. Your broker derived the misleading 25% growth figure by inappropriately using the arithmetic mean to calculate the performance of your investments (100% - 50%) / 2 = 25%). In fact, your investment averaged 0% growth over two years.

Geometric Mean

In the example above, your broker should have used the geometric mean, not the arithmetic mean. The geometric mean is defined as the nth root (where n is the count of numbers) of the product of the numbers. In contrast, the arithmetic mean or average is defined as the sum of the numbers divided by the count of the numbers.

Use the geometric mean function whenever several quantities are multiplied together to produce a product. In the original example, your investment earned 100% the first year and lost 50% the second year. The average rate of return is not found by calculating the arithmetic mean, which would imply that in the first year, your investment was multiplied (not added to) by 2 (1+100%); and that in the second year, it was multiplied by 0.5 (1-50%). The true average rate of return is found by calculating the geometric mean of these two numbers, which equals 1, revealing that your investment earned no money over two years.

In general, use the geometric mean instead of the arithmetic mean in these cases:

• When you are charting growth rates or decay rates
• If the trend you are displaying is described by compounded growth (i.e. revenue growth, investment returns, inflation, bank interest, radioactive decay, U.S. debt growth, etc.)
• If you don’t think a 100% loss can be outweighed by any value later

Before we look at the MAQL statements for calculating the geometric mean, it is important to note that we can calculate the product of a set of numbers as follows:

Product Aggregate Function

1. Take the natural logarithm value ln() of the numbers.

2. Add these logarithmic values.

3. Finally, take the exponential of this value:

   
   

   For geometric mean, just divide the sum of logarithmic values by the number of records:

   
   

Now, let’s see how to write MAQL statements to calculate the geometric mean.

Scenario

Suppose that you want to find the average of revenue growth rates for the past five years.

Metric 1_ RevenueSum

To create a metric for revenue growth, we first need a metric to calculate the revenue in a given year and a metric to calculate the revenue of the previous year.

SELECT SUM( Revenue )

To get the revenue for this year, we can simply slice this metric by Year Sold.

Metric 2_ RevenueSumPrevious

To calculate the revenue for the previous year, we can use the following MAQL:

SELECT SUM( Revenue ) FOR PREVIOUS( Year (Year Sold) )

Note how we used FOR PREVIOUS to express the previous year.

Metric 3_ RevenueGrowth

Now that we have a metric for finding the revenue of a given year and its previous year, we can calculate the growth rate:

SELECT ( RevenueSum - RevenueSumPrevious )/ RevenueSumPrevious BY Year (Year Sold) , ALL OTHER

Note how we used the BY clause to lock this amount by Year.

Metric 4 - RevenueGrowth

Once we have a metric to calculate the growth rate, we want to calculate a metric that shows the growth rate relative to the starting amount. In other words, growth rate + 100%:

SELECT RevenueGrowth + 1

Metric 5 - Ln of RevenueGrowth

To calculate the product, we will take the natural log of our metric:

SELECT Ln( RevenueGrowth +1)

Metric 6 - SumofLn

Next, we calculate the sum of the logarithmic values:

SELECT SUM(Ln( RevenueGrowth +1) BY ALL OTHER

Note how we locked this value BY ALL OTHER clause so that it cannot be sliced by any other attribute.

Metric 7 - GeoMean

To calculate the geometric mean, we will divide the previous metric by the count of records and take the exponential of this value.

SELECT EXP( SumofLn /((SELECT COUNT( Year (Year Sold) , Records of Product ) BY ALL OTHER) -1))

Note how we used BY ALL OTHER to lock the count value. We subtract 1 from the count because the growth rate column will always have 1 fewer record than the total number of years in the dataset.

Metric 8 - AvgGrowthRate

Finally, we can calculate the average growth rate by subtracting 100% from GeoMean:

SELECT GeoMean -1