Conditional Formatting based on Month/Day values

  • 20 November 2019
  • 4 replies

Hi folks,

Relatively new to Looker (only a few projects under my belt so far) and I’m wondering if there is a way to conditionally format something like a bar in a chart (maybe changing the color or something similar) based on the date - we have an ask from the “powers that be” to see if we can display a different color for incomplete months on our org’s performance dashboard.

Has anyone done anything like this before, or is it even possible/reasonable?



4 replies

Hi @mswanson_nmdp,

Just to clarify, by incomplete months do you mean months where say for instance sales data is only taken from the 10th of a month until the end for instance (i.e. if you’re going back 6 months)?

If this is the case it may be useful for you to use advanced filters - if you’re new to Looker they’re nowhere near as intricate as you may think - so for instance you want to use the syntax “x months ago for x+1 months”. This sets your results so that the only incomplete month is the current one, and x months prior to the current one are complete - taking away your need for conditional formatting.

Does this help at all?

Thanks, Adam

Hi Adam,

Thank you for the tip, I think that’s really close to what I’m looking for.

We track our success against a few Org metrics (in our case, Lives impacted through our cellular therapies) but management wants to exclude the current month from a YOY growth visualization to increase clarity. So we would only show January through October for the current year, but then show January through December for the prior year.

As I’m typing this out, I’m realizing that a merged result set might be more what I’m looking for, with some If statements in table calculations to compare the month(now()) versus month(date()).

This does bring up a new question: Is there some sort of a guideline or a “hitchhiker’s guide” (for lack of a better term) that explains the best time to use each of the features or functions of Looker? I feel like I’m close to changing my middle name to “PDT” based on the number of times I fall back on them as a solution - is this an acceptable use?


One other thing, too - I’m wondering if there is any way (either current or planned) that I can apply those conditional or advanced filters to the visualization itself. Meaning that I’d like to change the color of a month that’s not complete, or apply some sort of dotting or dashing to denote that it’s not yet 100% complete.


As far as the YOY problem, maybe you need something like this in your filter:

This covers the current year’s complete months and the months from the previous year - from there you can do YOY growth using table calculations.

PDT-wise we use a few, I think it’s acceptable although I think some of the guys on here might know a bit more than I do in terms of do’s and don’ts around them though!

Hope this helps!