topic Background Colours in a Chart in QlikView

Background Colours in a Chart

Fri, 02 Nov 2012 09:37:14 GMT

Hello,

I am designing a KPI table which has the last 6 weeks, a weekly target figure, a base figure and a ytd figure (see below). Basically I want to apply RAG colours against the last 6 weeks and the ytd figure but not the base and target (they should have a simple white background). I don't think this is possible with the visual cues so I'm guessing is going to require a expressions against each KPI for the background colour but again I can't get Target and Base to remain white. Please note that the RAG colours for Week 1-6 and YTD are based off the target and base figures. I have also tried Custom Format Cell but it is not working either (I'm using 10 SR3)

Thanks,

Ralph

Header 1	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Target	Base	YTD
Sales	23	56	21	34	64	45	45	64	45
Attendance	5%	6%	9%	4%	3%	9%	6%	6%	5%

Re: Background Colours in a Chart

Gysbert_Wassenaar — Fri, 02 Nov 2012 11:35:28 GMT

I assume you only have one dimension and the rest of the columns are expressions. In that case you can set a color per expression by expanding the + sign in front of the expression, click on Background Color and in the Definition field put an expression to get the color you want, like blue() or rgb(0,0,255).

Re: Background Colours in a Chart

Fri, 02 Nov 2012 11:44:48 GMT

Yes, I have one dimension which is week. Below you can see the expression, Base, YTD and Target are all in the Week field and I look at the last 6 weeks with a variable. So I'm not entirely sure how I can replicate that into an if statement to give the rgb values for all 'Weeks' apart from Base and Target?

Sum

({<Week={"YTD","Base","Target",$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Sales)

Re: Background Colours in a Chart

Gysbert_Wassenaar — Fri, 02 Nov 2012 12:25:15 GMT

Are week 1 to week 6 values of a dimension or are they expressions? Is header 1 a dimension? Are Sales and Attendence values of a dimension or are they expressions?

Re: Background Colours in a Chart

Fri, 02 Nov 2012 12:33:42 GMT

The only dimension I have is week. I had to create a variable for weeks 1 - 6 so that the user could enter a date into an input box and then see the previous 6 week's worth of data. The base, target and ytd date will always remain constant no matter what date they input. There is no issue with the data, it is simply a cosmetic issue as I only want colours assigned to weeks 1 - 6 and then YTD, not base or target as they are the baselines on which the KPIs are calculated.

In the background colour of each expression I need to calculate:

if(expressiontotal) is less than the basetotal, then RED,

if(expressiontotal) is more than the targettotal then GREEN, everything else AMBER

but I don't want the Base or Target week to be calculated.

I hope this makes sense!

Re: Background Colours in a Chart

Gysbert_Wassenaar — Fri, 02 Nov 2012 13:10:37 GMT

Can you share the qvw you're working on?

Re: Background Colours in a Chart

Fri, 02 Nov 2012 13:31:41 GMT

Unfortunately I can't as it's sensitive information. I'll have a think about it and see if I can display the information in a different way. Thanks for your help