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