Multiple conditions for colour formatting

[GROM]

Hi guys,

I'm trying to do some conditional colour formatting for a table. While I can do a basic 'If,then,else' condition, I'm looking for something a bit more complex.

In the image I have attached, I want the columns showing percentages to display in the following manner:

• Points related to REVENUE: positive - blue, negative - red;
• Points related to EXPENSES: positive - red, negative - blue;

Should I try to nested IF statements with the NIVEL 1 and then select the points manually (ex. Nivel 1 - 1. Ventas,etc), or Set Analysis.

I'm a bit lost to be honest, so any help would be appreciated.

Thanks in advance.

[AndrewHughes · Accepted solution]

It sounds like the issue is the color should be applied to records flagged Expense differently than those flagged Revenue.

First I would create a flag RecordType with values 'Revenue', 'Expense'.

Then I would use it in your if statement. Something like this.

If(Flag.RecordType = 'Revenue', $(vNested1), IF(Flag.RecordType = 'Expense', $(vNested2), YELLOW())

vNested = If(Value > 0 , BLUE, RED)

vNested = if(Values<0, BLUE, RED)

[GROM]

Thanks for your answer Andrew.

However, and please correct me if I'm wrong, FLAGs can only be defined in the loading script, which we don't have access to. 

The idea was to try this myself before asking our Qlik partner to do it. Oh well 😛

[AndrewHughes]

If that is the case, it is possible to create a very large if statement with each field manually listed; however, it is advisable to do this in the script so that the developer can for example just join color RED to Expenses and color Blue to Revenues. This way going forward as new accounts are added they are automatically colored.