Question

How to format selected measures when switching measures via parameters

  • 25 October 2017
  • 3 replies
  • 1256 views

The introduction of parameters in Looker allows folks the ability to swap between dimensions / measures in their visualizations, with added functionality that did not exist before.



Although similar functionality existed with templated filters, what makes parameters more robust is its label_from_parameter. Variable measures can now take the name of whatever parameter value is selected by the user. E.g., from a list of 3 measures, you pick a measure called “revenue”, and a measure called “revenue” will land in your table visualization, instead of “variable measure”.



Currently, Looker cannot “inherit” measure formatting of selected measures when flipping between different choices. However, there is a workaround, which I detail below. This includes





  • Using liquid in a sql case statements to 1) Select a Measure 2) Inject the Measure in a visualization


  • Using liquid in an html if statement, to select the desired formatting



    • You must reference the parameter as if it were a filter with _filters[‘parameter_name’]








Use case of 3 measures, that includes whole numbers, measures with 1 decimal place, and $ values with 2 decimal places


- For whole numbers, use {{ rendered_value | round }}


- For measures with one decimal place, use {{ rendered_value | round: 1 }}


- For $s with two decimal places, use ${{ rendered_value | round: 2 }} or ${{ rendered_value }} if you’ve set the value_format of your variable_measure to “0.00”



Important: Make sure that you pick a value_format with the most amount of decimal places that any of the selectable measures can contain. The use of round in the liquid variable can only remove decimal places, but cannot add any.



Please see example below



Happy looking!



##@@@@@@@@@@

##PARAMETERS

##@@@@@@@@@@



parameter: measure_type {

allowed_value: {label: "Sales Revenue $" value: "Sales Revenue $" }

allowed_value: {label: "Activations" value: "Activations" }

}



##@@@@@@@@@@

##MEASURES

##@@@@@@@@@@



measure: variable_measure {

type: number

label_from_parameter: measure_type

sql: case

when {% parameter measure_type %} = 'Sales Revenue' then ${sales_rev}

when {% parameter measure_type %} = 'Activations' then ${activations}

end

;;

html: {% if _filters['measure_type'] == 'Activations' %} {{ rendered_value | round }}

{% else %} ${{ rendered_value }}

{% endif %}

;;

value_format: "0.00"

}

3 replies

Thank you for the post, this is very helpful! Have you noticed when using this method that the formatting does not carry over to the labels in the visualization? I am running into an issue where the data results are showing the proper value format ($1.5B) and when I hover over the visualization the detail formats are correct, but my visualization value labels and axis labels are not showing the additional formatting (they still show 1.5B).

Userlevel 5
Badge

Hello @Laura_Burns and welcome!



the HTML rendering is not applied to labels in visualization (it’s applied to the tooltip though as you mentioned).



To apply to labels and axis, you need to use value_format or value_format_name

Thanks @Cyril_MTL_Analytics. Based on that it seems that with liquid measures it is not possible to have the value labels or axis in the visualization appear as the correct format with different value format types? In my visualization I am switching between two unit metrics (volume and pieces) and one dollar metric (spend). I don’t believe there is a way to have both dollars and units specified in the value_format or value_format_name, correct me if I am wrong. This is currently how I have it written in my LookML


parameter: metric_selector {



type: string

allowed_value: {

label: "Volume"

value: "volume"

}

allowed_value: {

label: "Pieces"

value: "pieces"

}

allowed_value: {

label: "Spend"

value: "spend"

}}



measure: metric {

label_from_parameter: metric_selector

type: number

value_format: "[<999950]0,\"K\";[<999950000]0.0,,\"M\";0.0,,,\"B\""

sql:

CASE

WHEN {% parameter metric_selector %} = 'volume'

THEN ${volume}

WHEN {% parameter metric_selector %} = 'pieces'

THEN ${pieces}

WHEN {% parameter metric_selector %} = 'spend'

THEN ${spend}

ELSE NULL

END ;;

html:

{% if metric_selector._parameter_value == "'spend'" %}

${{rendered_value}}

{% else %}

{{rendered_value}}

{% endif %}



;;


}

Reply