Change background on change of pivot table dimension

[#1]

Hi all,

I want to have a different background colour for each dimension of a pivot table (table only has one dimension). The number of values in the dimension obviously changes and I want to spread the colours over the RGB scale. For example, there are say 100 values in the dimension and after selections only 11 are left. 255 divided by 11 means the RGB adjuster is 23 so dimension value 1 might be something like RGB(23, 232,255) , the next RGB(36, 209,255) and the 11th RGB(253, 2, 255).

But how can I identify when the value of the dimension changes? I tried the 'above' function for comparing but no luck.

Any thoughts?

Regards,

Gordon

[Anonymous]

Gordon,
I think it's easier to assign RGBs in the script using some more or less reasonable algorythm. After that, no pain on the front end. Except that in some cases colors will be too close to tell the difference...

[#3]

Hi Michael,

Thanks for the reply. You are absolutely right in that if I use the 'total' number of values, in my example 100, then the RGB value is only adjusted by 2 which is indistinguishable. Trying to do it with the dynamic number of dimension values (where 11 is typical) gives much bigger adjustment (23) but its knowing when the dimension changes.

I have a variable that is defined as

=floor(255 / count(distinct AreaID))

so I know the adjust value, but its knowing when the dimension changes.

I have also tried using 'rand'

=rgb(rand()* 255, 255 - (rand() *255), 255)

and it least it does provide distinction between the dimensions (unless very unlucky!)

Regards,

Gordon