Making the default value of a parameter dependent on Liquid logic

liquid
low_priority
done
reply
(Menashe Hamm) #1

We’re using this for some of our count(distinct measures (content simplified from actual):

  parameter: how_to_count_uniques {
    type: unquoted
    allowed_value: { label: "exactly"  value: "count" }
    allowed_value: { label: "approximately"  value: "hll" }
    default_value: "hll"
  }
  measure: unique_widgets {
    type: number
    sql: {% parameter how_to_count_uniques %}(distinct ${widget_id}) ;;
  }

(The hll function in my dialect can be used as a quick, approximate alternative to count(distinct.)

As you see, if someone doesn’t filter on the parameter at all, he will be using hll as a default. I’d like to change that default, so that it depends on Liquid parameters such as view1.foo._in_query or date_start. For example, if a query has view1.foo in it and doesn’t choose how_to_count_uniques, then it should use hll; if the query doesn’t have view1.foo and doesn’t choose how_to_count_uniques, then it should use count(distinct.

I’d like to do this without always_filter (because many queries don’t look at metrics affected by how_to_count_uniques and requiring it will confuse users).

Any suggestions, please?

0 Likes

The Podium — April 18th, 2019
(Izzy) #2

I think this would be hard to do in the way that you describe — You could do it in a sql_always_where, like you mentioned, and build it out with {% if %} logic, which is probably what I would do. But that wouldn’t surface it on the frontend as well…

But yeah, I would probably make the default value no_filter_selected and then using some liquid if statements (either in the measure SQL or in a sql_always where) build out some kind of complex logic about when to do what, and what filter to ultimately apply.

Note: sql_always_where will at least not confuse users too much, since it won’t be applying a filter that they see or altering the results outside of what you want.

0 Likes

(Menashe Hamm) #3

Thanks, @izzy! I’ll look into doing it using sql_always_where.

0 Likes

(Menashe Hamm) #4

Actually, I don’t see how to do it using sql_always_where. I can certainly edit it into each count-distinct measure using Liquid logic, violating DRY.

0 Likes

Support for assigning Liquid variables
(Ben Silverstein) #5

Menashe, what about using in_query Liquid in your measure, and setting the default value as the string “default”?

sql: 
{% if view1.foo._in_query %}

  {% if parameter how_to_count_uniques == 'default' %} 
    count(distinct ${widget_id}}
  {% else %}
    {% parameter how_to_count_uniques %}(distinct ${widget_id})
  {% endif %}

{% else %}
  {% parameter how_to_count_uniques %}(distinct ${widget_id})

{% endif %}
0 Likes

(Menashe Hamm) #6

Thanks, @bens. That’s what I meant by

and may be what I’ll wind up doing.

0 Likes

(Ian) #7

Got something a little wacky to try, if you are using a derived table (or you could create another view which adds no data but is always joined it) create a UDF of the type of count distinct you wish in the derived table sql, you can put your liquid logic in there. Every measure can then just be myUDF(value) and the definition of the UDF changes depending on what the person is doing. I doubt this would work flawlessly as people would be overwriting the UDF for each other but its a starting point for some kind of nasty hack.

2 Likes

(Ian) #8

or create a udf which receives the type of cnt distinct you want to perform and every measure can pass the value and the type (might have to use a filter instead of a param and use the sql always where to set it).
…again not fully thought through or tested but some things to try

1 Like

(Menashe Hamm) #9

@IanT interesting ideas. Thanks a lot!

0 Likes