# Qlik Sense App Development

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Valued Contributor

## Re: Help with nested IF statement in conditional formatting

HI, 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

Highlighted
Valued Contributor

## Re: Help with nested IF statement in conditional formatting

HI, 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