Dynamic background colour for bar chart

[#1]

Hi guys,

Need some help with this.

I have some data and it contains fields such as Question 1, Question 2 all the way to 10.

Each question has a score of 1 to 4 with 1 being poor and 4 being excellent.

The information is represented in a bar chart for each question and I am coding the colours as follows:

Chart 1

if(Questions_1_Score  = 1, red(),

                                  if(Questions_1_Score =2, rgb(255,128,0),

                                        if(Questions_1_Score =3, yellow(),

                                            if(Questions_1_Score =4, green()))))

Chart 2

if(Questions_2_Score  = 1, red(),

                                  if(Questions_2_Score =2, rgb(255,128,0),

                                        if(Questions_2_Score =3, yellow(),

                                            if(Questions_2_Score =4, green()))))

Chart 3

if(Questions_3_Score  = 1, red(),

                                  if(Questions_3_Score =2, rgb(255,128,0),

                                        if(Questions_3_Score =3, yellow(),

                                            if(Questions_3_Score =4, green()))))

Now I don't want to write 10 expressions that are the same thing with only the dimension name changing.

Is there a way to have just one expression that can cater for all 10, and if possible, any suggestions please?

Thank you

[rubenmarin]

Hi, if you have a name for your expression, you can use this name, i.e., if the expression is named 'Score' you can use:

If([Score] = 1, red()....

If you already have a field named 'Score' you can rename your expression to a unique expression name (you can add a blank space at the end)

[marcus_sommer]

It looked that you used crosstable-structures which needs often more efforts and ressourcen to calculate and display things. Therefore my suggestion to transform the table, see here what is meant: The Crosstable Load.

- Marcus

[#4]

Thanks Ruben.

My expression is different from the background colour expression.

My expression is a simple sum of a different field, while the expression used for the background colour is what I have included in my question.

[nagarjuna_kotha]

Hi,

In chat Expression background Try this code.

=if(Score  = 1, red(),

  if(Score =2, rgb(255,128,0),

   if(Score =3, yellow(),

    green())))

[rubenmarin]

Ok, another option is using a parametrized variable. Not exactly the same expression but easier to maintain...

Create a variable with your expression with the number as parameter:

if(Questions_$1_Score  = 1, red(),

if(Questions_$1_Score =2, rgb(255,128,0),

  if(Questions_$1_Score =3, yellow(),

   if(Questions_$1_Score =4, green()))))

$1: The first parameter of the variable call

Then you can set this BGColor expression:

Chart 1: $(variableName(1)) //Replaces '$1' with '1'

Chart 2: $(variableName(2)) //Replaces '$1' with '2'

This way, if you want to change colors or values, you only need to change the variable value instead of every expression.

[#7]

Thanks Ruben.

I have come up with an alternative, by creating some variables in the script using the expression and using them instead.

Many thanks