Question

Conditional Formatting Bottom 10%

  • 7 September 2016
  • 12 replies
  • 528 views

Userlevel 1

Not super great at liquid HTML. I have used conditional formatting for static numbers like > 95% turns it green for everything. Is there a way to do conditional formatting so that out of a dataset the bottom 10% of are highlighted?


12 replies

Userlevel 3
Badge

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?

Userlevel 1

Redshift is the sql we are using

Userlevel 3
Badge

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!

Userlevel 1

Awesome!! Thanks



So if I am already creating a derived table to house my data I thin I can just fold in the percent rank portion of it as a new field.



What I am not clear on is how to reference that percentile field to highlight the actual price in your case. In your example I would want to display the retail price, but have it highlighted if percentile was the bottom 10%. I attempted to do something similar in the past but couldn’t figure out the syntax in liquid to reference another field. Below is some liquid I had used before:



      {% if 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 %}



I assumed I would need to change out the value portion and reference another column from the dataset but couldn’t get that to work. Can you help explain how that should work as well?



Thanks so much

Userlevel 3
Badge

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.

Userlevel 1

Awesome I’ll try that out. Thanks!

Userlevel 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?

Userlevel 1

Looks like it is happening only in some random instances where there are null values. But not all instances of null

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!

Userlevel 1


Cool thanks I’ll send it over.

Userlevel 1

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```
Userlevel 1

I was already doing that. Here is my actual SQL:


PERCENT_RANK() OVER (PARTITION BY ord.calendar_day, SUM(1.0 * pdr.PERFECT_ORDERS_HOUDINI/NULLIF(pdr.ORDERS_SHIPPED_HOUDINI,0)) IS NOT NULL ORDER BY SUM(1.0 * pdr.PERFECT_ORDERS_HOUDINI/NULLIF(pdr.ORDERS_SHIPPED_HOUDINI,0)) ASC) PERCENT_PDR_HOUDINI



Even if that wasn’t the case why would it show up properly on the looker interface from the web but not when emailed out?

Reply