# 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 %};; }