Dynamic yesno using filter

Looker Q&A Looker
,
1

Hey,

My goal is to create a yesno dimension based on whether or not a row’s value is ≤115% and ≥85% a filter’s value.

dimension: received_comparable {
sql:
{% if (received * 0.85) >= _filters[‘acquirerbinconversiondailysummary.received’] %}
yes
{% if (received * 1.15) <= _filters[‘acquirerbinconversiondailysummary.received’] %}
yes
{% else %}
no
{% endif %} ;;
}


This is my attempt to do so, however I am getting an error.

Do I need to set up a parameter instead of a Liquid or use a PDT?

Thanks
1 Like
2

Hi johnjosi,

What I understand from your question is that you want a user to input a numeric value. With that input, you’d like to create a boolean dimension stating true if (i) received * 0.8 >= user input or (ii) received * 1.15 <= user input, otherwise false.

Please correct me if I’ve misunderstood.

If my understanding is correct, you can do this with parameters

  1. Create a parameter, which is a filter-only field for the end user, where they can input any numeric value
  2. Use the value inputted by the user in your yesno dimension calculation
parameter: input_received_comparison {
   type: number
}

dimension: received_comparable {
  type: yesno
  sql: case when ${received} * 0.85 >= {% parameter input_received_comparison %} then true
      when ${received} * 1.15 <= {% parameter input_received_comparison %} then true
      else false end ;;
}

I hope this is a solution for you.

Thanks,

Naomi

1 Like
3

Thanks @Naomi_Johnson I’ll give this a try and let you know!

4

No worries. Please do let me know, woud love to know if it’s the solution you need

5

Hi @naomi

The parameter was the right solution. I had to switch your recommendation from true to false for the yes/no conclusion to work.

dimension: received_comparable {
  type: yesno
  sql: case when ${received} * 0.85 >= {% parameter input_received_comparison %} then true
      when ${received} * 1.15 <= {% parameter input_received_comparison %} then true
      else false end ;;
}

Also, I couldn’t get multiple comparable parameters and dimensions to work properly. Is there a technical limitation? This is the code I used:

​​​​​

dimension: atv_comparable {
  type: yesno
  sql: case when ${atv} * 0.5 >= {% parameter input_atv_comparison %} then false
      when ${atv} * 1.50 <= {% parameter input_atv_comparison %} then false
      else true end ;;
}

Thanks for your help