Conditional formatting of total seems incorrect if non-total rows contain a null value

I have a percent column with conditional formatting on it “where value < 1”, and this formatting is also applied to the column total.

If all rows contain values, the total row is formatted properly.

If one or more rows contain nulls, the total row is not formatted properly, even though its value is correct and visually satisfies the condition.

It’s like the value the condition is checking is null, whereas the value displayed is not null.

The consequence is that the row total format is an unreliable call to action, as one has to scroll up and down to view all rows to see if any are conditionally formatted…

1 Like

When you say all rows, do you mean all the dimension values, or all the measure values? I ran some tests with some null dimension values and some null measure values, and couldn’t get it to format weirdly.

Could you give some more detailed steps to reproduce? I might have missed something.

I narrowed down the issue a bit more. The conditional formatting of the total cell is only incorrect when its value is zero. Maybe there is a specific bug around the handling of nulls vs. 0 for total conditional formatting?

In order to get nulls to show up in the column, the measure must be on a child view, left outer joined onto the explore. I was unable to reproduce using a single view and just sticking null values into the cells because Looker applies a coalesce(value, 0).

I came up with an example that reproduces the issue. I added two identical columns to illustrate the issue: percent_done1’s total is 0% and it is not conditionally formatted properly, whereas percent_done2’s total is 50% and it is conditionally formatted properly. Further, the non-total cells of the percent_done1 column are conditionally formatted properly even when they equal zero. The conditional formatting is identical for both columns, “is less than 1” (meaning, less than 100%).

21%20AM

Code and dashboard source follow…

view: child {
  derived_table: {
    sql:
      select * from (values
        (10, 1, false),
        (20, 2, false)
      ) t (id, parent_id, is_done)
    ;;
  }
  
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  
  dimension: parent_id {
    type: number
    sql: ${TABLE}.parent_id ;;
  }
  
  dimension: is_done {
    type: yesno
    sql: ${TABLE}.is_done ;;
  }
  
  measure: count {
    type: count
  }
  
  measure: count_done {
    type: count
    filters: {
      field: is_done
      value: "yes"
    }
  }
  
  measure: percent_done {
    type: number
    value_format_name: percent_0
    sql:  floor(100.0 * ${count_done} / nullif(${count}, 0)) / 100.0 ;;
  }
}

view: parent {
  derived_table: {
    sql:
      select * from (values
        (1),
        (2),
        (3)
      ) t (id)
    ;;
  }

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
}

explore: parent {
  join: child {
    type: left_outer
    relationship: one_to_many
    sql_on: ${child.parent_id} = ${parent.id} ;;
  }
}

And here’s the LookML dashboard export:

- dashboard: test
  title: test
  layout: newspaper
  elements:
  - title: test
    name: test
    model: fusion
    explore: parent
    type: table
    fields: [parent.id, child.percent_done1, child.percent_done2]
    sorts: [child.percent_done1 desc]
    limit: 500
    total: true
    query_timezone: America/New_York
    show_view_names: false
    show_row_numbers: true
    truncate_column_names: false
    subtotals_at_bottom: false
    hide_totals: false
    hide_row_totals: false
    table_theme: editable
    limit_displayed_rows: false
    enable_conditional_formatting: true
    conditional_formatting: [{type: less than, value: 1, background_color: "#ed6168",
        font_color: !!null '', color_application: {collection_id: legacy, palette_id: legacy_diverging1,
          options: {constraints: {mid: {type: number, value: 0}}, mirror: true}},
        bold: false, italic: false, strikethrough: false, fields: [child.percent_done1,
          child.percent_done2]}]
    conditional_formatting_include_totals: true
    conditional_formatting_include_nulls: false
    listen: {}
    row: 0
    col: 0
    width: 8
    height: 6

Oh, interesting. Thanks for the detailed walkthrough, Steve. I’ll get it reproduced internally and then patched up— Updates will be here!