Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
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).
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)
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?
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!
Can you share the qvw you're working on?
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