Question

Elegant solution for Missing Values in numeric dimensions?

  • 19 January 2022
  • 2 replies
  • 136 views

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!

 

 


This topic has been closed for comments

2 replies

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

Userlevel 7
Badge +1

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} ;;

  }

}