Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Background colour set in expression

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

A

22 Replies
colinh
Not applicable

Background colour set in expression

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?

Not applicable

Background colour set in expression

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

colinh
Not applicable

Background colour set in expression

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

Not applicable

Re: Background colour set in expression

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))))))

Not applicable

Background colour set in expression

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)))))))))))

colinh
Not applicable

Background colour set in expression

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)))))))))))

Not applicable

Background colour set in expression

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

colinh
Not applicable

Background colour set in expression

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.

stigchel
Not applicable

Background colour set in expression

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

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