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

Announcements

Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: **Register**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Conditional colouring

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

paul_scotchford

Specialist

2016-07-17
10:34 PM

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

Conditional colouring

I should know the answer to this, but I seem to be having a problem with a solution, basically I have a straight table of only 3 rows, and I want to conditionally colour code (text) the **2nd** and **3rd** row field 1 based on how they compare to Row 1 field 1 e.g.

**Row 1 field 1** stays black,

If** Row 2 field 1** is greater than **Row 1 field 1** then colour is red , if same then orange else green.

There is a single dimension that is used e.g. Category , so in effect I am testing the values across the dimension rows.

I don' want to write the calculations i.e. the SUM's etc into the colour condition as this becomes clumsy.

Anyone tried this before ? I need an AHA moment

Thanks

Paul

643 Views

1 Solution

Accepted Solutions

paul_scotchford

Specialist

2016-07-17
11:40 PM

Author

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

Ok here is my solution after a quick walk around the block in the sunshine

if([Avg Cost] < above([Avg Cost],if(RowNo()=3,2,1)),Green(128),

if([Avg Cost] = above([Avg Cost],if(RowNo()=3,2,1)),Yellow(128)

,if([Avg Cost] > above([Avg Cost],if(RowNo()=3,2,1)),Red(128)

,Black()

)))

So Note, My table has only 3 rows and will always be 3 rows.

402 Views

9 Replies

Anonymous

Not applicable

2016-07-17
11:03 PM

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

G'day Paul,

I put a flag in the load script and did the comparison there

Load

...

IF( 'row' > PREVIOUS ('row'), 1,0) as myFlag,

....

From

...

order by dimension

then in the chart I put

IF (myFlag = 1, red(), yellow())

cheers,

402 Views

Not applicable

2016-07-17
11:15 PM

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

Write this expression in your Background color

if(Amount = Above(Amount), Yellow(),

if(Amount > above(Amount), Red(), Green()))

402 Views

paul_scotchford

Specialist

2016-07-17
11:27 PM

Author

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

No sorry, my point perhaps is not clear, The First Row in the table is what I am comparing my

ROW 2 and ROW 3 against .

402 Views

paul_scotchford

Specialist

2016-07-17
11:40 PM

Author

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

Ok here is my solution after a quick walk around the block in the sunshine

if([Avg Cost] < above([Avg Cost],if(RowNo()=3,2,1)),Green(128),

if([Avg Cost] = above([Avg Cost],if(RowNo()=3,2,1)),Yellow(128)

,if([Avg Cost] > above([Avg Cost],if(RowNo()=3,2,1)),Red(128)

,Black()

)))

So Note, My table has only 3 rows and will always be 3 rows.

403 Views

Not applicable

2016-07-17
11:44 PM

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

Got It

if(Amount = above(Amount, RowNo()-1), Yellow(),

if(Amount > above(Amount, RowNo()-1), Red(), Green()))

402 Views

agni_gold

Specialist III

2016-07-17
11:54 PM

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

This can also reduce effort , check if required.

402 Views

Anonymous

Not applicable

2016-07-18
12:06 AM

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

Hi Paul,

In Text Color option:

You can add an expression like this:

If(IsNull(Above(sum(Value))) = 0,

if(sum(Value) > Above(sum(Value)), red(),

if(sum(Value) = Above(Sum(Value)), RGB(255,128,0), green())), Black())

This expression evaluate the previous value, if it's not null it will check if the current value is greater or equal than the previous and apply the corresponding color code, otherwise font will be black. Here is an example of the output of this function:

Hope it helps.

- Karla

402 Views

Not applicable

2016-07-18
12:06 AM

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

Yes Agnivesh. That makes it even more simple.

if(Amount = FieldValue('Amount',1), Yellow(),

if(Amount > FieldValue('Amount',1), Red(), Green()))

402 Views

paul_scotchford

Specialist

2016-07-18
05:51 AM

Author

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

Thanks for all the ideas, the one I posted is working the way I want it

402 Views