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