Pivot Table - Visual Cue. Help

[#1]

Hi,

I am currently working on a project which compares data for each month. Each month the project is refreshed and a new month will appear in the pivot table showing the new costs attached to a certain reference.

I am looking to create a visual cue where if the cost decreases (compared to the previous month) then the cell will go green, if it increases it will go red.

I have attached an example project but I have no idea how to get the visual cue to work especially as the months are ongoing.

Many thanks in advance.

[#2 · Accepted solution]

Hi Ted,

don't use the visual cues for this one, but expand the node of your expression where you can put expressions to defined text colour, background colour etc. There you go with the following line of code:

if(Sum(Cost) < Before(Sum(Cost)), RGB(0,255,0))

I wanted to attach your adjusted file, put for some reason I receive error messages from the webpage during the upload.

cheers

Florian

[#3]

Florian,

Cheers for that, works perfectly.

[#4]

Is there any way to get the first value that is greater than 0 or NULL to appear in a separate colour whilst maintaining the other rulesets for the background colours?

Many thanks

[#5]

Hi Ted,

try this one as expression for your background colour:

if(Sum(Cost) < Before(Sum(Cost)), RGB(0,255,0), if(Before(Sum(Cost))=0 AND Sum(Cost)>0, RGB(255,0,0)))

cheers

Florian

[#6]

Hi Florian,

I can't seem to get this to work, where it seems to fall over is how to identify the first values in the month (1/12/10). The 'before' function doesn't work for it.

Essentially im trying to colour the start of brand new values as Blue, as the value changes over time, if it decreases the new value will be green and if it increases the new value will be red.

Cheers

[johnw]

Maybe this?

if((len(before(sum(Cost)))=0 or before(sum(Cost))=0) and sum(Cost)>0,lightblue()
,if(sum(Cost)>before(sum(Cost)),lightgreen()
,if(sum(Cost)<before(sum(Cost)),lightred())))

It looks a little funny to me, in that if you go to 0 it's red since you decreased, and the next time you get a number it'll be blue again, like you're starting over.

[#8]

Hi Ted,

not sure if I understand right: You want the whole column of Dec 2010 be in blue or as John suggested the first value above 0?

cheers

Florian

[#9]

Many thanks to everyones help on this.

John I used your suggestion and swapped the lightred and lightgreen around. I then added text colour: -

if

((

(

sum(Cost) < 1 AND before(sum(Cost)) < 1 OR len(before(sum(Cost))) = 0 AND sum(Cost) < 1 , RGB(255,255,255) , RGB(0,0,0) )

This hides any 0 values that is prior to any value of significance.