How to Make a Weighted Average

  • 22 June 2022
  • 0 replies
  • 803 views

Userlevel 5
Badge
  • Looker Staff
  • 172 replies

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

The Problem


We want to make a weighted average.
 

The Solution


In this example, we want to create a very basic customer health comparison by taking the average of each customer's order prices and weighting them by how recently each order was placed.

First, we give each order a numerical weight, giving a higher weight to more recent orders:

dimension: weight {
type: number
sql:
CASE
WHEN ${days_since_order} < 30 THEN 3
WHEN ${days_since_order} < 60 THEN 2
ELSE 1
END ;;
}

From here, we can calculate a weighted price by multiplying the weight by the price, and finally creating the weighted average of this weighted price:

dimension: weighted_price {
type: number
sql: ${sale_price} * ${weight} ;;
}

measure: weighted_average {
type: number
sql: sum(${weighted_price})/sum(${weight}) ;;
}

The result is a weighted average, which focuses on recent orders:

 


This topic has been closed for comments