Conditional Formatting Bottom 10%

Hey there Joseph! It is possible to do something like this, but how it is implemented depends a lot on your SQL dialect. Can you let me know what SQL dialect you are on?

You can calculate a notion of percentile in a window function in Redshift, which would require you to create a derived table. Though there are many steps to this, the pattern itself is not too complicated and can be used in many different ways. A simple derived table that would calculate this for you could look something like this:

- view: product_percentiles


  derived_table:


    sql: |


      SELECT


        products.id AS "id",


        products.retail_price AS "retail_price", -- needed to calculate percentile


        PERCENT_RANK() OVER (ORDER BY products.retail_price DESC) AS "percentile"


      FROM public.products AS products


      GROUP BY 1, 2 -- remember to group by non-aggregate fields


  


  fields:


    - dimension: id


      sql: ${TABLE}.id


      primary_key: true


      hidden: true


      


    - dimension: percentile


      type: number


      sql: ${TABLE}.percentile

This will calculate the percentile of each product according to its retail price. The critical part is this SQL (which must be in a derived table to work):

PERCENT_RANK() OVER (ORDER BY products.retail_price DESC) AS "percentile"

Then, I’ll join the percentile table into my Products table (which is one to one, since I have on record per product in both tables:

- explore: products


  joins:


    - join: product_percentiles


      relationship: one_to_one


      type: full_outer


      sql_on: ${products.id} = ${product_percentiles.id}

Finally, I can reference the percentile field in my Liquid HTML to color the rows based on percentile (which is now exposed as a dimension in LookML and can be referenced in Liquid).

The limitation of this approach is that the percentile is not sensitive to the filtering of the data, but you can use templated filters to make sure that you’re only calculating percentile over the records that are relevant to the current query. See this doc for more information about templated filters - personally one of my favorite Looker features!

Yeah, you’re on the right track there! If you need to refer to the value of another column, you can do something like this:

{% if percentile._value < 98 TOTAL' %}


        <div style="color: white; background-color: #CD5555; text-align:center">{{ rendered_value }}</div>


      {% else %}


        <div style="text-align:center">{{ rendered_value }}</div>


      {% endif %}

EDIT: It occurs to me that you’ll probably want percentile._value < .98, since the percent_rank function returns a number between 0 and 1.

Not sure this is a bug or if something else is wrong but seems more like a bug. When I view the page in Looker it shows up as expected, but when I schedule as an inline table and send there are instances where cells are highlighted in the email that aren’t in Looker. Any thoughts?

Hi Joseph! Would you mind sending screenshots of what you’re seeing to help.looker.com so we can troubleshoot the specific issue you’re seeing? Adding the LookML would help, as well. Thanks!

Hi Joseph, I think you need to add a CASE WHEN to the SQL in the PDT to handle those Nulls. Adding to Max’s example:

       products.id AS "id",


       products.retail_price AS "retail_price",


       (CASE WHEN products.retail_price IS NOT NULL


		THEN PERCENT_RANK() OVER (ORDER BY products.retail_price DESC) 


		ELSE 0


		END)


		AS "percentile"


     FROM public.products AS products


     GROUP BY 1, 2```