Elegant solution for Missing Values in numeric dimensions?

Hi all,

I know there’s a few solutions for this, but they feel clunky and am wondering if I’m missing a more elegant solution to this problem.

I have a dimension, called NPS, which is of type: number, and has a known range: [0, 10]. I want to, for example, create a bar chart with count per dimension value, and I want to show the full range of values. However, in some cases there may not be any records for a certain dimension value, hence no row is shown, and the bar chart ends up omitting the value.

I know this can be solved with “fill in missing values”, however this requires changing the dimension to type: tier or case, which also has it’s disadvantages:

  • Missing values can only be filled if 1 dimension is used, so not possible with more complex datasets
  • The dimension values are strings, making subsequent manipulation of them (such as performing a table calculation on the values) more complex

Ideally, I’d like to be able to specify the possible values without needing to change the data type.

But if that’s not possible, I’m wondering what other solutions there may be, using whatever other functionality Looker has to offer.

Thanks!

6dd95010-c93f-4a28-b869-02732fc1c4b9.png
0 2 395
2 REPLIES 2

JMJ1
Participant II

Hi,

Another option is to define a dimension (view) with the possible 10 numeric values, and add a full outer join in the explore.

explore: my_table {

  join: user {

    sql_on: ${my_table.nps} = ${dim_nps.nps} ;;

    type: full_outer

  }

}

Best regards

Dawid
Participant V

If your dialect was BigQuery and you couldn’t do this in your Data Model in any way I would use a dirty hack of creating an auxillery view, sort of like a dictionary table:

view: scores {

  derived_table: {

    sql: SELECT * FROM UNNEST(GENERATE_ARRAY(1, 10)) AS score ;;

  }

}



and then in the explore

explore: scores {

  view_label: “NPS”


  join: nps {

    type: left_outer

    relationship: one_to_many

    sql_on: ${nps.score} = ${scores.score} ;;

  }

}
Top Labels in this Space
Top Solution Authors