Dynamic yesno using filter

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 4 728
4 REPLIES 4

Naomi_Johnson
Participant III

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

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

Naomi_Johnson
Participant III

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

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

Top Labels in this Space
Top Solution Authors