Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot tables conditional colours

Hi Guys

I have a pivot table built , where I have 3 colour conditions I wish to apply - intially I was going to do this on Visual Cues tab, but as there are 3 conditions I dont think I can.

So I was going to do it in the expressions, I have tried,

if ([total] < 100, (RGB 0,128,0),if ([total] > 100 and < 115, (RGB 255,255,0),if([total] > 115,(RGB 255,0,0))))

But its just not working the condition is

Less than or equal too 100 , text color Green

between 101 and 115 , text Orange

and greater than 115 text is Red.

Can anyone please help ?

Thanks

1 Solution

Accepted Solutions
pat_agen
Specialist
Specialist

hi,

this should do it

if ( [total] <= 100, RGB (0,128,0),                 // if less than 100 than green

     if ([(total] <= 115, RGB (255,255,0),        // else if less than 115 then orange

          RGB (255,0,0)))                             // else red

rgb() is a function so teh parenthisis go around the arguments.

bewrae that id your total has got decimal places you will have to tweak the conditions.

View solution in original post

7 Replies
pat_agen
Specialist
Specialist

hi,

this should do it

if ( [total] <= 100, RGB (0,128,0),                 // if less than 100 than green

     if ([(total] <= 115, RGB (255,255,0),        // else if less than 115 then orange

          RGB (255,0,0)))                             // else red

rgb() is a function so teh parenthisis go around the arguments.

bewrae that id your total has got decimal places you will have to tweak the conditions.

Not applicable
Author

Pat Thanks so much

Anne

Not applicable
Author

Hey Pat

I cant quite get the above to do what I want , I have the chart formatted as decimals and it doesnt seem to work I have tried changing the range to 0.1 and 0.115, but it stil isnt working can you please advise ?

Thanks

pat_agen
Specialist
Specialist

hey,

what do you mean you cannot get it to do exactly what you want?

What kind of values are you getting in the [total] as this is what we are comparing against? Can you show some of those values here?

Is it a decimal? why have you changed the range to 0.1, 0.115? Is [total] a percentage? or do you have values like 100.1, 115.05 etc. ?

you can remove the decimal part in the conditional expression by doing this floor([total]) without touching your actual expression.

Sometimes these color codings can be misleading especially on the boundaries as the underlying number may be different to the displayed figure - due to rounding/formatting etc.

could you post an example of your qvw?

Not applicable
Author

Hi pat

I orignally used this statement on a pivot chart loaded from excel where it ws formatted as a number , as ws the chart in Qlikview.

I wish to use it now in another Pivot chart where the data in Excal is formatted as Percentage and in chart its as percentage with 1 decimal place .

This is the statement as amended:

if ([2.1.2 FTE Manpower v Actual (Budget = 107)] <= 0.1, RGB (0,255,0), if ([2.1.2 FTE Manpower v Actual (Budget = 107)] <= 0.115, RGB (255,255,0),RGB (255,0,0)))

It is resulting in all the background colours being RED ( Which is the or else part of the condition) 

Do you reckon its something with how the data is formatted in the load ?

Thanks

pat_agen
Specialist
Specialist

well everything is certainly being evaluated as being larger than 0.115, that is for sure!

it is hard to tell without seeing the data but as said before formatting means what you see is not necessarily what you get.

In your document do a right click and "select fields" and selct the filed you are measuring aginst. thsi will give you a list box withe the values in the field as they appear with their default formatiing.

what does this look like?

Not applicable
Author

Perfect , Great Tip Pat ,

Its all ok now

Thanks again