topic Re: Strange issue when using mod to control the dimension background color in Pivot table in QlikView

Strange issue when using mod to control the dimension background color in Pivot table

uacg0009 — Wed, 20 Apr 2016 05:57:54 GMT

Hi All,

I meet a strange issue with the background color in Pivot table.

I have data and table :

In this pivot table, I have 2 dimensions : Country and City, and I move the City to the top

and I also add =if(Mod(ColumnNo(),2)=1,Yellow(),LightGray()) to the dimension - City - Background

But it didn't show what I want, I don't know why, and I try to add a new expression(=columnno()), then it shows what I want :

is it a bug? I don't know why and I just want to know how to fix that.(Well, I don't want the columnno() expression actually)

You can use the attached.

Thanks.

Aiolos Zhao

Re: Strange issue when using mod to control the dimension background color in Pivot table

uacg0009 — Wed, 20 Apr 2016 07:21:51 GMT

Anyone can help?

Re: Strange issue when using mod to control the dimension background color in Pivot table

marcus_sommer — Wed, 20 Apr 2016 07:37:19 GMT

If you disabled surpress NULL within the tab presentation it will work. The reason for not showing the coloring within the horizontally dimensions are that all values within first row return NULL for these dimension. Unfortunately there are depencies between the cell-values and the coloring and not only within expressions else between expression and dimensions.

As alternatively you could try to return always a none NULL value like with the columnno() or something else. But these could be difficult and also have side-effects like the surpressing NULL options. Quite useful is it often to replace (all) NULL's within the datamodel with real values like 'NULL', 'EMPTY', 'MISSING', '#NV' or similar which has also further advantages, for example that these values are now selectable. For this have a look here: NULL handling in QlikView

- Marcus

Re: Strange issue when using mod to control the dimension background color in Pivot table

uacg0009 — Wed, 20 Apr 2016 08:08:34 GMT

Hi Marcus,

Thank you so much for your reply, now I know the reason why the color shows wrong.

But I didn't find the suppress NULL in presentation tab, I only know the suppress When Value is NULL in dimension tab, but it didn't work.

Could you please tell me how to solve that at the front-end? Because the real data is too big, I don't want to change the datamodel.

Aiolos Zhao

Re: Strange issue when using mod to control the dimension background color in Pivot table

marcus_sommer — Wed, 20 Apr 2016 08:17:43 GMT

It's in the bottom left area:

- Marcus

Re: Strange issue when using mod to control the dimension background color in Pivot table

sunny_talwar — Wed, 20 Apr 2016 08:22:04 GMT

Seems to work as pointed out by Marcus

Re: Strange issue when using mod to control the dimension background color in Pivot table

uacg0009 — Wed, 20 Apr 2016 08:24:53 GMT

Oh, Suppress the Zero Value, thank you so much!

Aiolos Zhao