How To Dynamically Format Selected Measures via Liquid Parameters in Looker

Looker gives users the ability to swap between dimensions or measures in a visualization by the use of Liquid Parameters. To learn more, explore Looker’s documentation on Liquid Parameters here

Currently, when a user selects measure from the parameter filter, it does not pass through the value format given for that measurement. In the following use case, we had a choice of three measures with different formatting needing to be applied. The formats were:

  • a whole number 
  • two decimal places and ‘%’
  • whole numbers preceding with a $ and including a comma when more than hundreds

To get the value formats passed through for each measure selected, you need a HTML parameter using the liquid parameter value in if-else statements with the following formatting:

Format Value Wanted

HTML Formatting

25

{{ rendered_value | round }}

0.25%

{{ rendered_value | round: 2 | append: % }}

$25,000

${{ rendered_value }}

Round simply rounds a number to the nearest integer or, if a number is passed as an argument, to that number of decimal places.

See the example below of the code and output for this use case:

parameter: metric_selector {
    type: string
    description: "Used as a filter to switch between KPI's"
    allowed_value: {
      label: "ARR"
      value: "arr" 
    }
    allowed_value: {
      label: "Won Count"
      value: "won_count"
    }

    allowed_value: {
      label: "Win Percentage"
      value: "win_percentage"
    }
      }

  measure: metric {
    label_from_parameter: metric_selector
    description: "Value corresponding to KPI in Metric Selector"
    type: number
    value_format: "#,##0" -- default value format for the ‘else’ values
    sql:
    CASE
      WHEN {% parameter metric_selector %} = 'arr'
        THEN ${arr}
      WHEN {% parameter metric_selector %} = 'won_count'
        THEN ${won_count}
      WHEN {% parameter metric_selector %} = 'win_percentage'
        THEN ${win_percentage}
      ELSE NULL
    END
    ;;
  html:
  {% if metric_selector._parameter_value ==  "'won_count'" %}
  {{rendered_value | round }} -- rounds value to whole number
  {% elsif metric_selector._parameter_value == "'win_percentage'" %}
  {{ rendered_value | round: 2  | append: "%" }} -- rounds to that number of decimal places and appends a ‘%’ after the value
  {% else %}
  ${{ rendered_value }} -- takes the default value format defined above and adds a ‘$’
  {% endif %}
  ;;
  }

Screenshots of each measure before the html parameter is applied:

Before1
Before2
Before3

Screenshots after the html is applied:

After1
After2
After3

Happy Looking!

Related Blogs

 

Red Pill Analytics, an Innive Company, is a Looker Consulting Partner. From proof-of-concept to implementation to training your users, our Looker-certified consultants can help. Or, if you are interested in guidance while working with Looker, Snowflake, Fivetran, or any of your data or analytics projects, feel free to reach out to us on our website or find us on Twitter, Facebook, and LinkedIn.

 

Leave a Reply

Your email address will not be published. Required fields are marked *