Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: Help with nested IF statement in conditional formatting

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

1 Reply
luismadriz
Valued Contributor

Re: Help with nested IF statement in conditional formatting

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

Community Browser