Conditional formatting max 20 rules - can you have more?

[Andy2734]

Hi Forum,

The conditional formatting for a chart has a max of 20 rules. Can you have more? I´m creating a view for a lab app that would have something like 50 rules for 27 different samples, mainly color coding the values that exceed the allowed limits. There´s a lot of different columns that need to have different rules for different samples, using yellow and red color codes to change the backgrounds for different values.

Is there a way to add the quota for rules, either by obtaining it from Looker or to group somehow different rules for one rule? The difficulty in this is that there´s different samples with different value limits for the rule, so you can´t have the same rule easily adapted by just grouping them. 

Any ideas,

thanks for your help!

[lauratilton]

I assume the values are numbers? Would you be ok if they became text?

If so, you can set up calculated fields (which can have a lot more rules) to concat different numbers of spaces on to the front of the value, then set up your conditional formatting to use "STARTS WITH" and the number of spaces. (Note that when setting up your formatting, the last set of true conditions found is the rule that is applied, so you'll put the rules in order from least to most spaces.)

Something like this (note I am using * to indicate spaces below to make this easier to see but you would just use spaces.)

CASE
WHEN SampleName="Sample1" and Value>=0 and Value <5 then concat("*",Value)
WHEN SampleName="Sample1" and Value>=5 and Value <10 then concat("**",Value)
WHEN SampleName="Sample1" and Value>=10 and Value <20 then concat("***",Value)
WHEN SampleName="Sample2" and Value>=0 and Value <10 then concat("*",Value)
WHEN SampleName="Sample2" and Value>=10 and Value <20 then concat("**",Value)
WHEN SampleName="Sample2" and Value>=20 and Value <40 then concat("***",Value)
END

Then your conditional formatting rules would be set up so anything starting with one space (*) is red, two spaces is yellow, three spaces is green. Or something like that, hopefully you get the idea!

[Andy2734]

Hi Laura and thanks for taking the time to provide assistance!

A few comments and questions:

•  Yes, I suppose it would be okay if the values are in text format.
•  Where can I add the formula you where suggesting? (CASE...END)
•  And for the conditional formatting rule, do I set it up with this formula as the field,   STARTS WITH and a number of spaces, for example 3?

Thanks for help!

[lauratilton]

You can use this type of formula to create a calculated field in your data source. Here's more info about calculated fields. https://support.google.com/looker-studio/answer/7569962?hl=en&ref_topic=7570421&sjid=733902967617543...

In terms of the conditional formatting, yes, you'd create rules with STARTS WITH and the number of spaces. (The order of the rules is important as I mentioned above.)

[Andy2734]

Hi Laura and thanks for your help! I finally had time to get back to this, sorry for the delay. I don´t completely get this formulation of the calculated fields: What should I have in the last "value" of each row, as the samples (Näyte=Sample) use different fields for the rules?

CASE
WHEN Näyte="3 Hapotus MeOH 188V075" and pH<2,3 or ph ❤️ then concat(" ",pH)
WHEN Näyte="3 Hapotus MeOH 188V075" and pH<2,4 and ph >=2,3 then concat("  ",pH)
WHEN Näyte="17 Polttoaine Metsälle 188V225" and Vesipitoisuus (%)>=3,5 and Vesipitoisuus (%) <5 then concat(" ",Vesipitoisuus (%))
WHEN SampleName="Sample2" and Value>=0 and Value <10 then concat("*",Value)
WHEN SampleName="Sample2" and Value>=10 and Value <20 then concat("**",Value)
WHEN SampleName="Sample2" and Value>=20 and Value <40 then concat("***",Value)
END

The error: Expected keyword THEN but got ","

[Andy2734]

It created a heart there: It should read < 3 😂

[lauratilton]

I'm guessing the error has to do with the comma in your values? I assume "2,3" is "2.3" just written the European way? Can you try decimals rather than commas?

[Andy2734]

Okay thanks, that sorted the error out. Do I need to add the calculated field as a dimension or metric, meaning that it will show in the chart? Is there a way of concealing it from the chart but using it for the formatting rule?

[lauratilton]

The point of this hack is to be able to display the value you want (in your case, pH) but then to conditionally format the pH value based on the rules. In this case, we built the rules in to the CASE statement, since there were too many for traditional conditional formatting, and instead used the spaces to dictate the conditional formatting. 

If you don't actually want to display the pH value, but instead you just want to conditionally format the Sample (Näyte) name based on the pH value, your formula should concat Näyte with the spaces and not pH as you show above. Then you use this calculated field instead of Näyte in your table (but you can still label it Näyte, it will display just like Näyte, but it will just have these hidden spaces that let you conditionally format.

Hope that helps!

[ashbait007]

I have the same case but I want to keep my numbers in an integer format so that I can sort it in the looker studio as per my need.

is there a way to color code for more than 20 rows but keeping it in integer format?

[HankinsonC]

Assuming I've understood your case properly, I'd just make a calculated field that read "under limit" and "over limit" (for example) and include it as another column in the table, and then format conditionally based on that. I would prefer to just take up more space than to do extra work to get all of the information into one column.  

[lauratilton]

Agreed. And if you don't want the calculated field to be displayed in the table, make the column super narrow, change the column title to "." and set the column formatting so that the text is transparent or matches the table color. So the calculated field will be in the table, and you can use it for conditional formatting, but you won't really see it.