topic Re: Excel conditional formatting in Qlik in QlikView

Excel conditional formatting in Qlik

sifatnabil — Thu, 19 Jan 2017 12:20:59 GMT

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%

Re: Excel conditional formatting in Qlik

Anil_Babu_Samineni — Thu, 19 Jan 2017 12:24:18 GMT

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

Re: Excel conditional formatting in Qlik

sifatnabil — Thu, 19 Jan 2017 12:27:12 GMT

Hi,

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

Re: Excel conditional formatting in Qlik

jonathandienst — Thu, 19 Jan 2017 12:35:48 GMT

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.

Re: Excel conditional formatting in Qlik

Anil_Babu_Samineni — Thu, 19 Jan 2017 12:39:11 GMT

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

Re: Excel conditional formatting in Qlik

sifatnabil — Thu, 19 Jan 2017 13:01:56 GMT

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?

Re: Excel conditional formatting in Qlik

jonathandienst — Thu, 19 Jan 2017 13:12:37 GMT

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.

Re: Excel conditional formatting in Qlik

sifatnabil — Thu, 19 Jan 2017 13:30:12 GMT

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))