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

[#1]

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

[Anonymous]

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.

Hope this is helpfull

[Anonymous]

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)

LOAD Week,

              AutoNumber(Week) as Counter

RESIDENT Master_Calendar;

Join (Master_Calendar)

Load Year,

          Max(Counter) as maxcounter

RESIDENT Master_Calendar

Group by Year;

Join (Master_Calendar)

LOAD Counter,

              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?

[#4]

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

Thanks

James

[rwunderlich]

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