Colormix Wizard

With the Colormix Wizard, opened from the File menu in the Edit Expression dialog, it is possible to create a colormix expression, i.e. an expression that calculates a dynamic color from a given measure.

It could e.g. be a calculated index with a value around 100 displayed in a pivot table. The higher index, the better. If a specific dimension value had an index that vastly exceeded 100, one would probably want to mark this pivot table line green, whereas red probably would be the appropriate color if the index value was much lower than 100. And one would want the color change to be gradual with yellow denoting 100. In other words – you want a dynamic color.

Such a gradual color calculation can be done with the Colormix functions in QlikView, but it can be cumbersome to create the appropriate expression inside the Colormix function. Here, the Colormix wizard can help.

First, it is important to clarify that the color functions, hence also the Colormix Wizard, are only relevant to use in place where QlikView expects a color function, i.e. not in the chart expression itself, but rather in the Background Color expression or in the Text Color expression.

When you open the Colormix Wizard from the File menu in the Edit Expression dialog, you will first see a page describing what is needed to create a dynamic color. If you want to skip the start page when you use the wizard in the future, mark the Don’t show this page again check box.

Click Next to continue.

Step 1 – Enter a Value Expression

This is the expression that will control what color QlikView will show. Typical expressions could be

  • Sum(Sales) / Sum(total Sales)

  • Sum(Sales) / Sum(Quota)

  • Avg(Age)

Note that it is usually a value that has the same order of magnitude no matter how many or how few records that you have selected. An average, a percentage or an index is usually a good measure to use.

Click Next to continue.

Step 2 – Set Upper and Lower Limits

In this page you need to define the upper and lower limits of the value expressions and the corresponding colors. ”Limit” meaning not the maximum possible value, but the limit where the maximum color is reached. Typical expressions could e.g. be:

Value Expression Upper Limit Lower Limit
Sum(Sales) / Sum(total Sales) Sum(total Sales) 0
Sum(Sales) / Sum(Quota) 2 (=200%) 0
Avg(Age) Max(total Age) Min(total Age)

But also other limits are of course possible.

If you use the color wizard within a chart, then the following two expressions are always a good choice:

  • RangeMax (top(total <ValueExpression>,1,NoOfRows(total)))

  • RangeMin (top(total <ValueExpression>,1,NoOfRows(total)))

These expressions will calculate the largest and the smallest row value of <ValueExpression> within the chart.

Auto Normalize If this check box is marked, QlikView will try to find proper upper and lower limits. In such a case, expressions for the upper and lower levels cannot be entered manually.
Upper Limit Here an expression for an upper limit must be entered, unless the Auto Normalize option is checked.
Intermediate Here it is possible to enter an expression for an intermediate level, linked to a third color.
Lower Limit Here an expression for a lower limit must be entered, unless the Auto Normalize option is checked.
Reverse This button reverses the colors for the upper and lower limits.

Click Next to continue.

Step 3 – Finalize

In this page you finalize the color mix expression.

Enhanced Colors If this check box is marked, a hysteresis transformation is applied on top of the value expression. This will increase the sensitivity in the mid-range so that the color is pushed towards the Upper and Lower colors.
Value Saturation Here it is possible to control the behavior if the value expression exceeds the upper or lower limit. If Use Upper (Lower) Color is marked, QlikView will use the color for the maximum or the minimum. If Use Standard Color is marked, QlikView will instead revert to the standard colors as defined on the Colors page.