Excel conditional formatting in Qlik

[sifatnabil]

Hi all,

Is there a way to auto-colour cells in Qlik similar to how Excel can without having to define each colour? e.g.

156.67	21.69	13.85%
110.98	22.43	20.21%
107.20	16.89	15.76%
100.12	25.05	25.02%
69.53	16.26	23.39%
58.45	13.48	23.06%
52.29	11.20	21.41%
49.49	11.39	23.02%
37.49	8.69	23.17%
32.49	6.35	19.54%

[Anil_Babu_Samineni]

Autocolor means what? In qlik, we have buckets to do this? What is the condition to do that in Excel

[sifatnabil]

Hi,

Excel seems to have the ability to automatically colour in the cells with this function, which is quite useful:

[jonathandienst]

Check out the Autocolor Wizard in the File menu of the expression editor. This helps to define an expression with colours graduated by value from one colour to another colour, such as the Excel colouring you show in your post.

You can then use this colour expression in the background colour property for the column.

[Anil_Babu_Samineni]

Excel Experts writes the Macros for built in suck kind of default. But Qlikview won't capture same as Excel and if you want to do in qlikview we may help you with Condition like <20% , >=20% - <=80% , >80% like these.

We have expression which called Color(FieldIndex('FieldName', FieldName)

In factm Color and FieldIndex developed same architecture which is in Excel

[sifatnabil]

Thanks, this works, but implementing a background color now prevents me from double-click sorting the columns on the straight table. Is this supposed to happen?

[jonathandienst]

Its not the background colour itself that prevents sorting, but the colour expression may include an expression function, like above(), that affects sorting.

You can test this by adding a trivial colour expression to sortable table (like

=If(Column(1) > 100, LightCyan())

The table will still sort.

[sifatnabil · Accepted solution]

Thanks jontydkpi‌ and loveisfail‌ for the help - I can conclude that using the Colormix wizard has functions that prevent sorting. The better solution I found was this, which allows sort:

ColorMix1( rank(total YOUREXPRESSION)/noofrows(TOTAL) , rgb(176,255,216),rgb(255,128,128))