The Problem

Imagine you have a dimension called sales, and you want to visualize your sales data and quickly aggregate it in different ways. For example, you might want to look at average sales (average), number of sales (count), or maximum sales (max). Maybe you don't want to be restricted to just sales; maybe you want to look at gross margin data as well. If that's the case, the following pattern should be very helpful for such analyses.

The Solution

The solution below shows how we can iterate through different dimensions and aggregates and measure the combinations accordingly:

The Code

In the code block below, we use the Looker field parameter, parameter, which allows us to filter on values and dynamically update LookML definitions.

We can also leverage the label_from_parameter parameter, which allows us to dynamically change the label of the dynamic measure in a visualization, as we see in the example GIF above.

Example

In this example, we have two parameters, measure_type and dimension_to_aggregate, which allow us to update the definition for the measure dynamic_agg. Depending on the combination of measure_type and dimension_to_aggregate, we aggregate an appropriate dimension in a different way.

parameter: measure_type {
suggestions: ["sum","average","count","min","max"]
}

parameter: dimension_to_aggregate {
  type: unquoted
  allowed_value: {
    label: "Total Sale Price"
    value: "sale_price"
  }
  allowed_value: {
    label: "Total Gross Margin"
    value: "gross_margin"
  }
}

measure: dynamic_agg {
  type: number
  label_from_parameter: dimension_to_aggregate
  sql: case when {% condition measure_type %} 'sum' {% endcondition %} then sum( ${TABLE}.{% parameter dimension_to_aggregate %})
    when {% condition measure_type %} 'average' {% endcondition %} then avg( ${TABLE}.{% parameter dimension_to_aggregate %})
    when {% condition measure_type %} 'count' {% endcondition %} then count( ${TABLE}.{% parameter dimension_to_aggregate %})
    when {% condition measure_type %} 'min' {% endcondition %} then min( ${TABLE}.{% parameter dimension_to_aggregate %})
    when {% condition measure_type %} 'max' {% endcondition %} then max( ${TABLE}.{% parameter dimension_to_aggregate %})
    else null end;;
}

If you're looking for a simpler solution where you can mix certain aggregation types, you can create separate (hidden) measures and reference those in a logical statement using the Liquid {% if %} syntax. Here's a little snippet to demonstrate:

parameter: main_metric_selector {
  type: unquoted
  allowed_value: {
    label: "Total Revenue"
    value: "total_revenue"
  }
  allowed_value: {
    label: "Total Users"
    value: "total_users"
  }
}

measure: total_revenue {
  hidden: yes
  type: sum
  sql: ${TABLE}.revenue ;;
}

measure: total_users {
  hidden: yes
  type: count_distinct
  sql: ${TABLE}.user_id ;;
}

measure: dynamic_measure {
  label_from_parameter: main_metric_selector
  sql:
    {% if main_metric_selector._parameter_value == 'total_revenue' %}
      ${total_revenue}
    {% else %}
      ${total_users}
    {% endif %};;
}