Color Mix Table with Multiple Expressions

[zjalalacssi]

Hello everybody,

i would like to apply a color mix on a table with 1 dimension and multiple expressions in order to have a gradient color  applied for all my measures.

Please find attached an example of my Table

Thank you

[Gysbert_Wassenaar]

Great! Sounds like a real good idea. Go for it! 

Ok, that's probably not why you opened this discussion.
Can you explain what your question is?

Have you tried searching for examples of the colormix function?
Does the gradient have to be calculated over the value of each separate expression or over all the expressions (i.e. they all have a common scale) ?

Unfortunately there's no colormix wizard in Qlik Sense like Qlikview has.  

[rubenmarin · Accepted solution]

Hi, I think the difficult is on retrieving the max value across all expressions.
Maybe with a variable defined as:
=Rangemax(
YourFistExpression,
YourSecondExpression,
YourThirdExpression,
...
)

And use this variable to calculate the % to use in colormix1:
ColorMix1(Expression/$(VariableWithMaxValueName), White(), LightRed())

Note htat the max value will be on totals, removing totals to retrieve max value only for rows can be done with an aggr() in each expression of the variable, wich probably gives a very poor performance.

[zjalalacssi]

Hello,

Thank you for you reply,

i was able to create the variable with the max value of all my expressions, the problem is, that the background color function is available for each measure, there is no background color function to apply for the whole table.

so i don't know exaclty where to put my colormix function

Thank you ^^ 

[zjalalacssi]

Hello, thank you for your reply

My request is to apply a colormix for the whole table with 5 measures, i know how to do it for one expression, but  i need to range all my expressions, and color the cells depending on the value behind :strong color for the highest and so on...

Thank you

[Gysbert_Wassenaar]

You'll have to apply it to each separate measure I'm afraid. As you said there's no setting at the table level.

[rubenmarin · Accepted solution]

Yes, you need to set each expression, all of them having the max value stored in variable as reference for 100%, you will also need a variable for min if values can be negative:

ColorMix1(Expression/$(VariableWithMaxValueName), White(), Color2)

Expression should be each one of the expressions:

ColorMix1(Count(Mail)/$(VariableWithMaxValueName), White(), Color2)

[zjalalacssi]

Hello,

it works perfectly, i was able to create a variable with range max of all my measures, and then apply the colormix in each measure background color

but still have an issue with the negative value.

will i create a rangemin variable also for all my measures?

and how to add it to my colormix expression please?

Thank you

[rubenmarin]

Yes, you will need another variable to store min value across al expressions.

Expression can be:
ColorMix1((Expression-$(MinVariable))/($(MaxVariable)-$(MinVariable)), White(), LightRed())

[zjalalacssi]

I think i have missed something

i have created 2 variables:

vRangeMax=Rangemax(exp1,exp2,exp3)

vRangeMin=Rangemin(exp1,exp2,exp3)

i have applied the colormix function for each measure

ColorMix1((exp1-$(vRangeMin))/($(vRangeMax)-$(vRangeMin)), White(), LightRed())

ColorMix1((exp2-$(vRangeMin))/($(vRangeMax)-$(vRangeMin)), White(), LightRed())

ColorMix1((exp3-$(vRangeMin))/($(vRangeMax)-$(vRangeMin)), White(), LightRed())

here's the result: