topic Pivot tables conditional colours in QlikView

Pivot tables conditional colours

Tue, 07 Jun 2011 13:30:25 GMT

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

Pivot tables conditional colours

pat_agen — Tue, 07 Jun 2011 13:38:03 GMT

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.

Pivot tables conditional colours

Tue, 07 Jun 2011 13:41:24 GMT

Pat Thanks so much

Anne

Pivot tables conditional colours

Tue, 07 Jun 2011 15:25:13 GMT

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

Pivot tables conditional colours

pat_agen — Tue, 07 Jun 2011 15:35:31 GMT

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?

Pivot tables conditional colours

Tue, 07 Jun 2011 15:42:27 GMT

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

Pivot tables conditional colours

pat_agen — Tue, 07 Jun 2011 15:58:30 GMT

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?

Pivot tables conditional colours

Tue, 07 Jun 2011 16:01:53 GMT

Perfect , Great Tip Pat ,

Its all ok now

Thanks again