Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

valera_sh
Contributor II

Background color for pivot table cells with MISSING values

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

Tags (4)
27 Replies
jjfabian
Contributor III

Background color for pivot table cells with MISSING values

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

valera_sh
Contributor II

Background color for pivot table cells with MISSING values

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

Pity... :-|

dandrew
New Contributor II

Background color for pivot table cells with MISSING values

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

Not applicable

Re: Background color for pivot table cells with MISSING values

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

Thanks

Not applicable

Re: Background color for pivot table cells with MISSING values

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 !

Not applicable

Re: Background color for pivot table cells with MISSING values

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

advait_thakur
Contributor III

Re: Background color for pivot table cells with MISSING values

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

Not applicable

Re: Background color for pivot table cells with MISSING values

Hi All,

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

Not applicable

Re: Background color for pivot table cells with MISSING values

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


Thanks
Arindam Roy

Community Browser