Background color for pivot table cells with MISSING values

[Anonymous]

Hi guys,

I have a pivot table and an expression for setting cells' its background color. It's just RED(), unconditionally.

Unfortunately, the MISSING values ignore my color and take the default one - white.

It is possible to override the default color for the expression by setting CUSTOM FORMAT CELL. But that is not a solution when I have many expression which I want to color differently according to dimension values.

The question is: how to make Qlikview consider the background color expression also for MISSING cells?

Thanks in advance.

Valera

[jjfabian]

Hi Valera,

how about trying to use an expression for your expression's background color. Something like:

If([Your Expression Here]>1, rgb(255,0,0),

     If([Your Expression Here]=1, rgb(255,255,0),

          If([Your Expression Here]<1, rgb(0,255,0), rgb(128,128,128))))

I guess you still have to put in the colors that you need (My example will give you a red, yellow, green ELSE light grey).

The most important part for your case would be the underlined part, which ultimately will control what color your missing values will get (if else statement=in your case --> missing values).

Hope this helps

Regards,

Jakob

[Anonymous]

I received a message from Qlikview. They say it is impossible to set the background cell color for MISSING values.

Pity... 😐

[Anonymous]

Hi Valera,

Not sure if this helps with your situation but I had a similar issue with a roster calendar and wanting to highlight the weekend columns. I resorted to using the following in the Expression to get rid of the null values rather than changing the code..

IF(ROSTER = 'Early' or ROSTER ='Middle'  or ROSTER = 'Late' or ROSTER = 'Training'  ,ROSTER, '      ')

The Background Colour expression =if(WEEKDAY(ROSTER_DATE) >4, Lightgray()) then highlights the whole column whereas before it only highlighted where there was a value. If your case you would put in more IF statements for the different colours.

I've also used this in the Date Dimension as previously it would only highlight the weekend if the cell below has a value.

You've probably worked this out for yourself - but just in case...

[#5]

Hi, do you have any news about this Qlikview Bug?

Thanks

[#6]

Same need, my expression returns 0 and this is considered as a missing value in the pivot table. As IsNull doesn't work, there is no way to detect the missing value ...

Any ideas are welcome !

[#7]

cook the missing value in the script, so it won't be missing any more

[advait_thakur]

Dear All,

This is to update you, This is a limitation in QlikView and considered working as designed. Even Isnull condition will not work for Missing Values and it will return ' - ' as QlikView engine does not understand, specially in Pivot tables it will not give the desired results.

Regards

Advait

[#9]

Hi All,

Any news on this. I need to color my rows in a pivot table where i have null values also.

[#10]

Can't we write some expression to change the color?

Thanks
Arindam Roy