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

Background Colours in a Chart

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 1Week 1Week 2Week 3Week 4Week 5Week 6TargetBaseYTD
Sales235621346445456445
Attendance5%6%9%4%3%9%6%6%5%
6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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)

Gysbert_Wassenaar

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?


talk is cheap, supply exceeds demand
Not applicable
Author

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!

Gysbert_Wassenaar

Can you share the qvw you're working on?


talk is cheap, supply exceeds demand
Not applicable
Author

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