Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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

  • 27 March 2019
  • 6 replies
  • 688 views
slooker
Userlevel 1

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…

6 replies

izzy
Userlevel 7
Badge +1

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.

slooker
Userlevel 1

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
izzy
Userlevel 7
Badge +1

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

JakeOsmond
Userlevel 2

I’m having a similar issue, my conditional formatting is highlighting the total rows differently (incorrectly) when there is a table calc involved. The two in the screenshot should be green when > 0.95 (95%) and red when below. This is worked out correctly on a row by row level but not for the total. Not sure if it’s because it’s a table calc?


Screenshot 2020-03-10 at 09.31.45


In all instances, so far, I’ve seen the total row copy what the first row’s formatting is


help!

JakeOsmond
Userlevel 2

To add to this, I can’t get rid of formatting for the total row now either, it’s jammed on!

izzy
Userlevel 7
Badge +1

This is a known bug with our new table visualization that has been fixed internally but not deployed yet. If you need it to work right now, you can use the “Table-Legacy” visualization where the functionality should be OK-- But we have fixed the issue and the fix should be present when v7.4 lands on instances, beginning next week.

Reply


Terms of Service