How do you reference a yesno dimension field value?

[sam8]

Knowledge Drop

Last tested: March 2021

Looker’s “yesno” dimensions return boolean values (TRUE or FALSE) rather than a string “yes” or “no”. For this reason, when SQL is involved, you don’t need to write “field == yes”, you can simply write “field”, as this will return TRUE or FALSE by itself. Here are specific examples depending on where you’d like to reference it:

SQL:

You can treat it as a boolean.

sql: CASE WHEN ${field} THEN ...

Filtered Measure:

  measure: first_purchase_count {      type: count      filters:[is_first_purchase:"Yes"]

Liquid HTML

{% if value == 'Yes' %} will do it.
Note: it is case sensitive.

Templated filter:

We put a boolean in between the condition, like:

derived_table: {sql:SELECT murder FROM public.rawhere {% condition filter %} murder=10 {% endcondition %} ;; }dimension: filter {type: yesnosql: ${TABLE}.murder=10;;}

cite docs for type yesno , docs for filtered measures and docs for liquid variables

This content is subject to limited support.                

[mweb]

Interestingly it will send ‘Yes’ or ‘No’ when using the Send functionality to send the dimension as a trait via Segment Identify.  Is there anyway to filter this or do we need to have a separate dimension of type string to send the true/false values?  I would expect true/false to be sent but for now have setup additional dimensions to get around this.

[sam8]

I think the additional dimensions are the best way to go here. 

The yesno dimensions are treated as true/false in SQL statements, but by the time the data is rendered and sent, they’ll only show “yes” and “no”. 

To double check, I tested setting “Results” to “As displayed in the data table” and “Values” to “Unformatted” and both returned Yes/No values.