topic Background colour set in expression in QlikView

Background colour set in expression

Wed, 08 Jun 2011 09:02:48 GMT

Hi Guys

I have a pivot table and I wish to set conditional background colours , but I need to set it on multiple selections,

Basically if

Field [Report] = CBIRE AND [Total] < 70 go Green

Field [Report] = CBIRE AND [Total] < 110 go Orange

Field [Report] = CBIRE AND [Total] > 70 go Red

AND

Field [Report] = ROI AND [Total] < 250 go Green

Field [Report] = ROI AND [Total] < 500 go Orange

Field [Report] = ROI AND [Total] > 500 go Red

I actually have another 4 rules based on different options in Report and Total to add in. But I have tried the first part using

if(Report='CBINT' and Total <=70,RGB (0,255,0),if(Report='CBINT' and Total<=110,RGB (255,255,0),RGB (255,0,0)))

but this is turning them all red regardless of what is filtered or not.

Can anyone please help ?

Thanks

Background colour set in expression

colinh — Wed, 08 Jun 2011 09:43:00 GMT

Using exactly your formula I get green, yellow, red.

It might be obvious, but in your first set of data you have CBIRE and in your calculation you have CBINT. Is it not that?

Background colour set in expression

Wed, 08 Jun 2011 09:48:47 GMT

Would you believe the samples that I had loaded had only RED range values loaded ( I know its often the simpliest issues that cause the greatest hassle !!!)

I've just started writing it with all 6 conditions , so please feel free to watch this space cause I reckon I will have more questions when Im joining all the ifs together

Thanks

Background colour set in expression

colinh — Wed, 08 Jun 2011 09:56:27 GMT

Just make sure you have as many closing brackets as opening brackets and you'll be fine.

Re: Background colour set in expression

Wed, 08 Jun 2011 10:13:55 GMT

Hey Colin

I have used the following and it works fine for all the drill downs ( So when I select a report).

But i need a condition where no reports are selected ( So basically the sum of each of the reports) the formula in bold in that portion but it does not seem to work , can you see if I have made a mistake ?

Or is there a way of saying if field is not selected ?

Thanks

Anne

if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if([Total]<=920,RGB (0,255,0),if([Total]<=1406,RGB (255,255,0),RGB (185,0,0))))))

Background colour set in expression

Wed, 08 Jun 2011 10:25:39 GMT

Hi Colin

I also took the opposite and tried when all Reports were selected see below in bold and again got same result all fields were red ,

if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=920,RGB (0,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

Background colour set in expression

colinh — Wed, 08 Jun 2011 10:43:41 GMT

What are you trying to highlight in red, a total on the chart, or each report? If it is a total, try this:

=if(Report='CBINT' and [Total]<=600,RGB (0,255,0),

if(Report='CBINT' and [Total]<=800,RGB (255,255,0),

if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),

if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),

if(Report='IRE' and [Total]<=50,RGB (0,255,0),

if(Report='IRE' and [Total]<=75,RGB (255,255,0),

if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),

if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),

if(sum([Total])<=920,RGB (0,255,0),

if(sum([Total])<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

Background colour set in expression

Wed, 08 Jun 2011 11:08:27 GMT

Hi Colin

I tried the above, what I wish to happen is , this formula is on a Pivot Chart,on a list box if I select CBINT and the Total is 700 I wish for the field to go Orange or if I select IRE and its 700 it will go red . ( Using my original statement this was happening)

( using for example there is only 2 reports) If I have not selected anything in the List box for report ( the pivot chart shows the totals) it uses the condition I would expect it to go Orange as the total is 1400 which under the last condition of the formula is < 1406 .

Whats happening with the above statement is when no fields are selected it is remaining green , and on the drill down only the ROI is actually working now

Thanks so much for your time on this

Background colour set in expression

colinh — Wed, 08 Jun 2011 11:48:49 GMT

No problem. I'm not sure I really understand your data - what is the Dimension on your pivot table? Is it Report, or some other dimension? Can you attach an example with your data structure and the chart.

I think the problem is that you are trying use the same expression to change the colour for each row and for the total. What might work better is to include the option for red in each condition, e.g.

if(Report='IRE' and [Total]<=50,RGB (0,255,0),

if(Report='IRE' and [Total]<=75,RGB (255,255,0),

if(Report='IRE' and [Total]>75,RGB (185,0,0),

Then the last condition will only apply to the total.

Background colour set in expression

stigchel — Wed, 08 Jun 2011 12:14:26 GMT

You can check the nr of selections (or none) like this:

if(GetSelectedCount(Field)=0,...,...)

Background colour set in expression

stigchel — Wed, 08 Jun 2011 12:30:20 GMT

It also might be a good idea to load the conditions/limits in fields linked to the report field, say 'Lower' and 'Upper'. You could then have a variable checking which report is selected, or none, and set the values.

i.e. Value for vLower=if(GetSelectedCount(Report)=0,vLower=920,vLower=Lower)

Expression for color:

if([Total]<=vLower,RGB (0,255,0),

if([Total]<=vUpper,RGB (255,255,0),

if([Total]>vUpper,RGB (185,0,0))))

Background colour set in expression

Wed, 08 Jun 2011 14:03:35 GMT

Hi Stigchel,

I have tried your suggestion , so what I wish to happen is that when no selection is Report the conditions <920 Green , <1406 Orange and >1406 Red. But now no condtional formating works at all, I know the first part of the formula works , as I've checked it . its just getting the condition when no Reports are selected from the drop down ( to be honest in some ways its actaully when all reports are selected in the data tho !!! perhaps this is the issue with the getselected fields !!

Can you help ?

if(Report='CM Banking INT' and [Total]<=600,RGB (0,127,0),if(Report='CM Banking INT' and [Total]<=800,RGB (255,128,0),if(Report='CM Banking INT' and [Total]>800,RGB (255,0,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,127,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,128,0),if(Report='PAYMENTS/I&I' and [Total]> 110,RGB (255,0,0),if(Report='IRE' and [Total]<=50,RGB (0,127,0),if(Report='IRE' and [Total]<=75,RGB (255,128,0),if(Report='IRE' and [Total]> 75,RGB (255,0,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=250,RGB (0,127,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=500,RGB (255,128,0),if(Report='RAIB Bank/AIB(UK)' and [Total]> 500,RGB (255,0,0),if(GetSelectedCount(Report)=0 and[Total]<=920,RGB (0,127,0),if(GetSelectedCount(Report)=0 and [Total]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

Background colour set in expression

Wed, 08 Jun 2011 14:31:40 GMT

Hi Stigchel

I have used your rational and in ths script put the following

if([Total]<70,'Green',if([Total]>70 and [Total]<110,'Orange','Red')) as [RAG Code],

so now in my condtional expression I can write it as

if([RAG Code]='Green',RGB (0,127,0),if([RAG Code]='Orange', RGB (255,128,0),RGB(255,0,0)))

Which again works great so long as I have a Report selected, can you help me add a condition that states when Reports is not specified ?

Thanks

Background colour set in expression

stigchel — Wed, 08 Jun 2011 14:35:30 GMT

There is a space missing between "and[Total]" in your previous to last post. Add a space and the expression will work for any single or none selection in report.

Background colour set in expression

stigchel — Wed, 08 Jun 2011 14:40:28 GMT

This still leaves you with the problem of multiple selections in report. You might want to control this by allowing only one selection (e.g. the option "always one selected value"). If you need the total as selection, load a crosstable with the report values and a total.

Re: Background colour set in expression

Wed, 08 Jun 2011 14:43:11 GMT

Hi Stigchel

The [Total] column is actually got some crazy name so for the sake of easy reading on the forum I have being pasting total across the name Below is the actual formula , I think there actually is a space there so I am double stumped !!!

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0), i

f(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0),

if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

Background colour set in expression

Wed, 08 Jun 2011 14:48:28 GMT

Hi Stigchel,

Due to this specific data there will only ever be one report selected , so I think if I can write the condition that says when no [RAG Code] follow condition < 920 Green , >1406 Orang < 1406 Red,

Going to try that now

Re: Background colour set in expression

stigchel — Wed, 08 Jun 2011 14:58:49 GMT

In the above expression one "if " is incorrectly broken off (line 8). I have corrected and used this expression succesfully in a document of mine (offcourse replacing your fields with ones that exists in mine)

Re: Background colour set in expression

Wed, 08 Jun 2011 15:05:08 GMT

Is the condition working when no fields are selected?

Mine are all just Red

( the above was just a paste error into this forum )

Background colour set in expression

stigchel — Wed, 08 Jun 2011 15:06:47 GMT

You can also load the values for R and G and B in seperate fields instead of a textual colour coding and in the color expression use:

=RGB(R,G,B)

If you have named these fields "R", "G" and "B"