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

[uacg0009]

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

[uacg0009]

Anyone can help?

[marcus_sommer]

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

[uacg0009]

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

[marcus_sommer · Accepted solution]

It's in the bottom left area:

- Marcus

[sunny_talwar]

Seems to work as pointed out by Marcus

[uacg0009]

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

Aiolos Zhao