Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

Announcements

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: **REGISTER NOW!**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Help with nested IF statement in conditional forma...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

sbass0601

Contributor II

2018-01-05
05:17 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Category | Current Month | Average 3 Periods | Variance from Average | Color Highlight |
---|---|---|---|---|

Income | -$1,000 | -$750 | -$250 | Green - RGB(198,224,180) |

Income | -$1,000 | -$1,250 | $250 | Red - RGB(242,220,219) |

Expense | $1,000 | $750 | $250 | Red - RGB(242,220,219) |

Expense | $1,000 | $1,250 | -$250 | Green - RGB(198,224,180) |

Gallons | $1,000 | $750 | $250 | Green - RGB(198,224,180) |

Gallons | $1,000 | $1,250 | -$250 | Red - 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?

926 Views

1 Solution

Accepted Solutions

luismadriz

Specialist

2018-01-05
11:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1 Reply

luismadriz

Specialist

2018-01-05
11:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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