User Custom Tiers with Parameters

  • 12 June 2020
  • 0 replies
  • 227 views

Userlevel 2

A common way to analyse groups of data is to use the tiers parameter in LookML. This will group a dimension as needed for a given set of tiers.


  dimension: sale_price {
type: number
sql: ${TABLE}.sale_price ;;
}

dimension: sale_price_tiers {
type: tier
sql: ${sale_price} ;;
tiers: [10,50,100,1000]
}

image


But you might need your users to be able to choose which tiers they want from the Explore/Look/Dashboard. You can do this using a set of parameters where your user can input their desired grouping (a custom tier).



The below LookML does the grouping on sale_price dimension mentioned above.


  parameter: bucket_1 {type: number}

parameter: bucket_2 {type: number}

parameter: bucket_3 {type: number}

parameter: bucket_4 {type: number}

dimension: bucket_groups {
sql:
{% assign bucket_string_1 = bucket_1._parameter_value | append: "," %}
{% assign bucket_string_2 = bucket_2._parameter_value | append: "," %}
{% assign bucket_string_3 = bucket_3._parameter_value | append: "," %}
{% assign bucket_string_4 = bucket_4._parameter_value %}

{% assign bucket_string = '0,' | append: bucket_string_1 | append: bucket_string_2 | append: bucket_string_3 | append: bucket_string_4 %}
{% assign bucket_array = bucket_string | remove: ",NULL" | split: "," %}
{% assign bucket_array_length = bucket_array.size | minus: 1 %}

CASE
{% for i in (1..bucket_array_length) %}
{% assign j = i | minus: 1 %}
WHEN ${sale_price} <= {{ bucket_array[i] }} THEN '{{i}}: {{ bucket_array[j] }} <= N < {{ bucket_array[i] }}'
{% endfor %}
ELSE
'5: {{ bucket_array.last }} +'
END ;;
html: {{ rendered_value | slice: 3, rendered_value.size }} ;;
}

First you define a number of parameters with type number. You can define the number of parameters as the number of choices you want to allow your users to select.


The liquid in the sql parameter then adds a comma to the end of all the groups and concatenates all the values together and also puts ‘0,’ at the start. In the example above, the bucket_string string would look like: “0,10,50,100,500”


We then remove “,NULL” from that string in case the user didn’t use all the parameters. Then we split the string into an array that we can loop through: [0,10,50,100,500].


We then assign a variable called bucket_array_length to be the length of the array minus 1.


When we loop through the array we can call the elements we need to write a CASE WHEN statement that includes the index. The index is used for ordering the dimension and then hidden in the html parameter using slice. If you want the ordering to be included you can just comment out or delete the html parameter. QED.


There is also another way we could implement this using 1 parameter and the user can type in any number of groups and comma separate them.



  parameter: bucket_single {type: string}

dimension: bucket_groups_single {
sql:
{% assign bucket_string = bucket_single._parameter_value %}
{% assign bucket_array = bucket_string | remove: "'" | prepend: "0," | split: "," %}
{% assign bucket_array_length = bucket_array.size | minus: 1 %}

CASE
{% for i in (1..bucket_array_length) %}
{% assign j = i | minus: 1 %}
WHEN ${sale_price} <= {{ bucket_array[i] }} THEN '{{i}}: {{ bucket_array[j] }} <= N < {{ bucket_array[i] }}'
{% endfor %}
ELSE
'5: {{ bucket_array.last }} +'
END ;;
html: {{ rendered_value | slice: 3, rendered_value.size }} ;;
}

This has a pro and a con.

Pro: It’s much less code and takes less room in the filters in the content

Con: User must comma separate their values, this needs to be made clear.


You can take this even further here! https://github.com/kevinMccarthyLooker/km_import_me/blob/master/custom_tiers.lkml


0 replies

Be the first to reply!

Reply