Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help with nested IF statement in conditional formatting

Hello!

We have a table that shows various items from our P&L for the current period, average prior 3 periods, and a column with the variance between the two.

We have sales P&L items with a credit balance (looks like a negative) and expenses with a debit balance (looks like a positive), but then we also have gallons sold (looks positive).

If the variance in sales & expenses are  negative, I want them to highlight green and red if the variance is positive.

If the variance in gallons are negative, I want them to highlight red and highlight green if the variance is positive.

I have grouped the GL accounts into [GL Type] = 'Income Expense' or 'Gallons' in order to try and get the conditional formatting to work.

Please see table below for a simplified example:

CategoryCurrent MonthAverage 3 PeriodsVariance from AverageColor Highlight

Income

-$1,000-$750-$250Green - RGB(198,224,180)
Income-$1,000-$1,250$250Red - RGB(242,220,219)
Expense$1,000$750$250Red - RGB(242,220,219)
Expense$1,000$1,250-$250Green - RGB(198,224,180)
Gallons$1,000$750$250Green - RGB(198,224,180)
Gallons$1,000$1,250-$250Red - RGB(242,220,219)

Here is the formula for conditional formatting I currently have (that's not working):

If((((Column(1))-(Column(2))<0) and [GL Type]='Income Expense')

RGB(198,224,180),

(IF(((Column(1)))-(Column(2)))>0) and [GL Type]='Income Expense')

RGB(242,220,219),

(IF(((Column(1)))-(Column(2)))<0) and [GL Type]='Gallons')

RGB(242,220,219),

(IF(((Column(1)))-(Column(2)))>0) and [GL Type]='Gallons')

RGB(198,224,180),

White()

Any suggestions?

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

HI,

Untitled.png

you could use this:

If(Column(1)-Column(2)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(1)-Column(2)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(1)-Column(2)<0 and Category='Gallons',RGB(242,220,219),

If(Column(1)-Column(2)>0 and Category='Gallons',RGB(198,224,180),White()))))

but if Column(3) already has the difference then you could use it:

If(Column(3)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(3)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(3)<0 and Category='Gallons',RGB(242,220,219),

If(Column(3)>0 and Category='Gallons',RGB(198,224,180),White()))))

I hope this helps,

Cheers,

Luis

View solution in original post

1 Reply
luismadriz
Specialist
Specialist

HI,

Untitled.png

you could use this:

If(Column(1)-Column(2)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(1)-Column(2)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(1)-Column(2)<0 and Category='Gallons',RGB(242,220,219),

If(Column(1)-Column(2)>0 and Category='Gallons',RGB(198,224,180),White()))))

but if Column(3) already has the difference then you could use it:

If(Column(3)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(3)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(3)<0 and Category='Gallons',RGB(242,220,219),

If(Column(3)>0 and Category='Gallons',RGB(198,224,180),White()))))

I hope this helps,

Cheers,

Luis