• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1479
  • Last Modified:

How do I create a QlikView expression which loops through the values, modifying the background cell colour ?

Hi,

I have a pivot table object in my QlikView application which sets the background colour of a column's cell according to its value.

The object checks the value of a field (Status_ID) and then sets the colour depending on the value of another field (SPI Status). The idea behind this method is that I can 'customise' the values to be checked against, for each column. The example below shows what I mean - the values checked against are held in two fields, Upper_Threshold and Lower_Threshold.

If (Status_ID = 'SS001',
   If ([SPI Status] >= Upper_Threshold, RGB(255,0,0),
     If ([SPI Status] >= Lower_Threshold, RGB(255,128,0),Green()))
,If (Status_ID = 'SS002',
   If ([SPI Status] >= Upper_Threshold, RGB(255,0,0),
     If ([SPI Status] >= Lower_Threshold, RGB(255,128,0),Green()))
,If (Status_ID = 'SS003',
   If ([SPI Status] >= Upper_Threshold, RGB(255,0,0),
     If ([SPI Status] >= Lower_Threshold, RGB(255,128,0),Green()))
,If (Status_ID = 'SS004',
   If ([SPI Status] >= Upper_Threshold, RGB(255,0,0),
     If ([SPI Status] >= Lower_Threshold, RGB(255,128,0),Green()))

However, as there are 30 Status IDs this has resulted in a huge multi-IF statement. I would like to reduce this code to a loop using a variable for the Status ID values.

How would I go about coding this ? How do I declare a local variable in my expression ? Or do I set it up outside my expression ?

Thanks
Toco
0
Tocogroup
Asked:
Tocogroup
1 Solution
 
RobOwner (Aidellio)Commented:
Are they all going to be the same?

why not just:

If ([SPI Status] >= Upper_Threshold, RGB(255,0,0),If ([SPI Status] >= Lower_Threshold, RGB(255,128,0),Green()))
0
 
TocogroupAuthor Commented:
Hah yes. Couldn't see the wood for the trees.
Thanks for that. Works great.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now