Hopefully quick question about calculated colours!

[Anonymous]

Hello,

I'm trying to dynamically change colours in a pie chart based upon the field in question - this actually contains the colour. I'd like

So there are 5 possible colours in my data - black, blue, green, red and yellow. All of these can be called directly using Black(), Blue() etc.

I've tried to calculate the colours using this as my expression:

=IMP.Colour & '()'

However nothing happens - the colours do not change. This doesn't work for changing the background of table cells either. Is it possible to do? I'd rather avoid If statements if possible seeing as the colours are so standard.

[rubenmarin]

Hi Jo, I made something similar but using RGB codes:

3 fields: RedBg, GreenBg, BlueBg

And dimension Background color expression as:

=RGB(RedBg, GreenBg, BlueBg)

[Anonymous]

Hey Ruben,

Sorry, I think I may not have been clear. There is a field called IMP.Colour in my dataset that actually contains the colour required as text - the possible values for the field are: Black, Red, Green, Yellow, Blue.

I thought that I could just concatenate this with () within the calculated colour expression so that the field value would be changed to Black(), Red(), Green() etc. I don't understand why it is not working however. When you just enter =Black() as the calculated colour, the colour changes to black.  So why doesn't      =IMP.Colour & '()'      change the colour to black if IMP.Colour = 'Black'?

I know I can achieve the same result using an IF statement but my other idea seemed so much cleaner and probably better for performance.

The equivalent IF statement would be:

Iff(IMP.Colour = 'Black', Black(),

     If(IMP.Colour = 'Red', Red(),

          If(IMP.Colour = 'Yellow', Yellow(),

               If(IMP.Colour = 'Green', Green(),

                    Blue()))))

[rubenmarin]

Sorry to say but I understand it right the first time.

If you try a Bg expression like: ='Red' & '()' it wouldn't work, so changing 'Red' by the value returned by IMP.Colour doesn't works either.

To concatenate values in that way you must use $-expansion but this is calculated before the table, so it's not row by row and won't give you the desired result.

As a workaround you can try to change your IMP.Colour to 3 fields to give the rgb code as said in my previous post.

Or maybe wait for another user gives you the solution building a color function like Red() or Blue() for a dimension background using a field value. I don't know how.

[rubenmarin]

If you don0t find another way, to make easy my workaround you can map the string color to the codes:

MAP_Colors:

Mapping LOAD * Inline [

Name, code

Black, 0;0;0

Red, 255;0;0

Yellow, 255;255;0

];

Data:

LOAD ... // [Your fields]

     IMP.Colour

     Subfield(ApplyMap('MAP_Colors', IMP.Colour), ';', 1) as IMP.ColourRed,

     Subfield(ApplyMap('MAP_Colors', IMP.Colour), ';', 2) as IMP.ColourGreen,

     Subfield(ApplyMap('MAP_Colors', IMP.Colour), ';', 3) as IMP.ColourBlue

FROM ....

Then you can use this bg expression:

RGB(IMP.ColourRed,IMP.ColourGreen,IMP.ColourBlue)

[sergiorey]

Hi Jo,

The expression =IMP.Colour & '()' does not work because qlkiview interprets this as a string and not as a function.

Instead of using IF statements, you could load a mapping table where you have something like this:

LOAD * Inline
[IMP.Colour, ColourFunction
Red, Red()
Black, Black()
White, White()
];

And then use the following expression for the calculated colour:

=$(=ColourFunction)

[simenkg]

Dont think you can do this directly, but the easiest way to do this is to create a variable:

set vColorBG = Pick(match($1 ,'Black','Red', 'Yellow', 'Green', 'Blue'),

  Black(),

  Red(),

  Yellow(),

  Green(),

  Blue()

  );

Then use $(vColorBG(Color)) as the backgroun color expression

[MarcoWedel · Accepted solution]

Hi,

one solution could be a color expression like:

=Pick(Match(IMP.Color,$(=Chr(39)&Concat({1} DISTINCT IMP.Color,Chr(39)&','&Chr(39))&Chr(39))),$(=Concat({1} DISTINCT IMP.Color,'(),')&'()'))

hope this helps

regards

Marco

[Anonymous]

Thank you everyone for the fantastic help! There were several different options but I went with this as it was the fastest to implement and only involved a quick tweak in the top level document...

Calculated background colour for the expression:

=Pick(Match(IMP.Colour,$(=Chr(39)&Concat({1} DISTINCT IMP.Colour,Chr(39)&','&Chr(39))&Chr(39))),$(=Concat({1} DISTINCT IMP.Colour,'(),')&'()')) 

Set the colours manually and checked 'Persistent Colors'

Sort by expression:

=if(IMP.Colour='Black',1,if(IMP.Colour='Red',2,if(IMP.Colour='Yellow',3,if(IMP.Colour='Green',4,5))))