Create a measure which calculates percentage difference between two dimensions

Looker Q&A Looker
1

Is there a way through LookML to workout the percentage difference between two numeric dimensions? Such that I can aggregate the dimensions down and the percentage difference works adjusts according to the granularity of the aggregation?

I know I can do this via a Table Calculation by creating two total measures and then doing a Table Calculation in the Explore between the two measures, but I preferably would like to do this in LookML directly.

dimension: current_kpi_value {

type: number

sql: ${TABLE}.current_kpi_value ;;

}

dimension: previous_kpi_value {

type: number

sql: ${TABLE}.previous_kpi_value ;;

}

2

You can either:

  1. make a new dimension that calculates a difference (or any row-by-row expression) and then apply a measure/aggregate to the new calculation dimension

  2. make measures/aggregates on top of each individual dimension, and then create a measure of type:number that calculates a difference (or any aggregate-vs-aggregate expression)

Since they may result in different logic/results, consider your use case carefully when deciding which you want

3

Perfect, thank you!