1

I am trying to find a way to visualize the max of a measurement over time. However, this measurement has multiple dimensions that it can be broken down by. Imagine the source data is a table like this where max_thing is an already pre-aggregated max value.

hourly_datetime max_thing country account_type
"2019-04-04 00:00:00 UTC" 280 US A
"2019-04-04 00:00:00 UTC" 175 UK A
"2019-04-04 00:00:00 UTC" 220 US B
"2019-04-04 00:00:00 UTC" 185 UK B
"2019-04-04 01:00:00 UTC" 250 US A
"2019-04-04 01:00:00 UTC" 173 UK A
"2019-04-04 01:00:00 UTC" 230 US B
"2019-04-04 01:00:00 UTC" 163 UK B

I make a view from this table with max_thing set as a measure of type max and country and account_type as a dimension of type string.

If I visualize this with the country and account_type dimensions included in the resulting dashboard, this works just fine. However, if a dimension like country is not included, the experienced behavior is that Looker takes the max value between the dimension breakdowns omitted and displays that. In this case, for account_type of A it would graph a two point line with the first value at 280 and the second value at 250. If I switch the time scale to daily instead of hourly, it would show a single point at 280.

What I actually want is that when a dimension like country is omitted, that the result is the max_thing value gets SUM()'d into the remaining selected dimensions, but then if I set looker to aggregate daily instead of hourly, that it still uses the MAX() function to find the highest max_thing for that day across all the hourly data points within that day. With the example here, that would mean the account_type of A would instead be two points of 445 and 423 on the hourly time scale, and when switched to daily, it would show 445 instead of 280.

Is there a way to achieve this in LookML dynamically that still works well with cases like this but with many more dimensions? (Trying to avoid having to precompute data source tables with every possible combination of dimensions.)

grg-n-sox
  • 717
  • 2
  • 5
  • 25

1 Answers1

0

I would address this issue using liquid to create a dynamic dimension/measure as below: You should first define two different measures (sum and max). Then pass the required measure when a specific dimension exists in the query:

## use sql_distinct_key for measures if needed
measure: max_things {
    type: max
    sql: ${max_things} ;;

measure: sum_things {
    type: sum
    sql: ${max_things} ;;

measure: dynamic_max_things {
    type: number
    sql: {% if country._in_query %}
    ${max_things}
    {% elsif daily_datetime._in_query %}
    ${max_things}
    {% elsif hourly_datetime._in_query %}
    ${sum_things}
    {% else %}
    ${max_things}
    {% endif %}
    ;;
Anita Hb
  • 118
  • 7