4 Replies Latest reply: Jan 10, 2012 8:22 PM by Rob Wunderlich

# Neater and tidier formulae for pivot chart with multiple coloured cells?

Using visual cues, you can use up to three colours to shade the cells in a pivot chart.

To get more than three colours, I use the Background Color property of the Expression and use multiple IF statements.

For example, my colour property at the moment is like this: =IF (expression > 1.25, RGB(100,100,100) , IF(expression >1, RGB(200,200,200) ,...) and so on. But because "expression" is a pretty long formula, the whole thing gets quite messy and long. And any changes to the expression can make it tricky to redo, especially if I have ten or so colours.

I was just wondering if there is a neater way, or a shortcut, to refer to the value in a specific cell in a table. So rather than have to recalculate, or refer to, the expression multiple times, is there a property so my formula can look something like: =IF(cellvalue > 1.25, RGB(100,100,100), ...).

Hope this makes sense

Thanks

James

• ###### Neater and tidier formulae for pivot chart with multiple coloured cells?

What you can do is use a calculation in your RGB() code.

Normally you fill in a number between 0 and 255, but you can also fill in a calculation. Just make sure that the outcome is between 0 and 255.

fe:

=RGB((255-(expression1)) , ((expression2)) , (100 +(expression3))   )

This works correct if you use, for example, expressions which calculate a percentage.

• ###### Re: Neater and tidier formulae for pivot chart with multiple coloured cells?

Here is something I tried:

I made with the dimension Week from my Maste_Calender table, but you can use it on any dimension you want.

First I add the below line to my script:

Join (Master_Calendar)

AutoNumber(Week) as Counter

RESIDENT Master_Calendar;

Join (Master_Calendar)

Max(Counter) as maxcounter

RESIDENT Master_Calendar

Group by Year;

Join (Master_Calendar)

Floor(Counter / maxcounter *255) as ColourCode

RESIDENT Master_Calendar;

Drop fields Counter, maxcounter from Master_Calendar;

This creates a field named ColourCode with a value from 1 to 255, based on the field Week

So now every week has its own colour code

Now you can use in any charts with Week as your dimension.

fe

=RGB(ColourCode, 0, 0)

OR

=RGB(ColourCode, (ColourCode /2), 200)

Let me know if this what you are looking for, Ok?

• ###### Neater and tidier formulae for pivot chart with multiple coloured cells?

Both neat suggestions and you have made me think about how I can vary the formula by altering the RGB bit instead.

Thanks

James

• ###### Re: Neater and tidier formulae for pivot chart with multiple coloured cells?

There is no reference to the "current cell" value available in an expression. But what you can do is put your color expression in a variable with param and use it as a function in the background expression.

In your script or the Variable editor:

SET vColorCell = if(\$1 > 100, red(), if(\$1>60, blue(), if(\$1> 40, yellow(), green())));

Then in the background expression:

=\$(vColorCell(yourExpression))

-Rob

http://robwunderlich.com