topic Re: Background color for pivot table cells with MISSING values in QlikView

Background color for pivot table cells with MISSING values

Anonymous — Tue, 31 Jan 2012 11:27:27 GMT

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

Background color for pivot table cells with MISSING values

jjfabian — Tue, 31 Jan 2012 15:15:13 GMT

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

Background color for pivot table cells with MISSING values

Anonymous — Thu, 02 Feb 2012 14:10:30 GMT

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

Pity... 😐

Background color for pivot table cells with MISSING values

Anonymous — Wed, 29 Feb 2012 09:51:54 GMT

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

Re: Background color for pivot table cells with MISSING values

Fri, 25 Oct 2013 19:04:50 GMT

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

Thanks

Re: Background color for pivot table cells with MISSING values

Thu, 27 Feb 2014 16:53:43 GMT

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 !

Re: Background color for pivot table cells with MISSING values

Wed, 19 Mar 2014 11:10:51 GMT

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

Re: Background color for pivot table cells with MISSING values

advait_thakur — Tue, 24 Jun 2014 08:12:44 GMT

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

Re: Background color for pivot table cells with MISSING values

Thu, 14 Aug 2014 12:47:16 GMT

Hi All,

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

Re: Background color for pivot table cells with MISSING values

Thu, 14 Aug 2014 12:50:07 GMT

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

Thanks
Arindam Roy

Re: Background color for pivot table cells with MISSING values

Thu, 14 Aug 2014 14:07:21 GMT

Sono fuori dall'ufficio da Lun 28/07/2014 fino a Dom 31/08/2014.

Nota: Questa è una risposta automatizzata al messaggio "Re: - Background

color for pivot table cells with MISSING values" inviata il 8/14/14 2:50:13 PM.

Questa è l'unica notifica che verrà ricevuta mentre la persona è assente.

Re: Background color for pivot table cells with MISSING values

Thu, 14 Aug 2014 14:07:21 GMT

Sono fuori dall'ufficio da Lun 28/07/2014 fino a Dom 31/08/2014.

Nota: Questa è una risposta automatizzata al messaggio "Re: - Background

color for pivot table cells with MISSING values" inviata il 8/14/14 2:48:18 PM.

Questa è l'unica notifica che verrà ricevuta mentre la persona è assente.

Re: Background color for pivot table cells with MISSING values

Mon, 18 Aug 2014 05:24:42 GMT

Is it like if there are more than two dimensions then the color pattern wont work? I am adding three dimensions then the color pattern is not working. Please advice.

Re: Background color for pivot table cells with MISSING values

Mon, 18 Aug 2014 08:04:22 GMT

Sono fuori dall'ufficio da Lun 28/07/2014 fino a Dom 31/08/2014.

Nota: Questa è una risposta automatizzata al messaggio "Re: - Background

color for pivot table cells with MISSING values" inviata il 8/18/14 7:24:53 AM.

Questa è l'unica notifica che verrà ricevuta mentre la persona è assente.

Re: Background color for pivot table cells with MISSING values

lft — Mon, 18 Aug 2014 09:21:09 GMT

Hi,

could you attach an example of simple qvw reproducing this issue ?

Thanks

Loic

Re: Background color for pivot table cells with MISSING values

Mon, 18 Aug 2014 14:07:23 GMT

Sono fuori dall'ufficio da Lun 28/07/2014 fino a Dom 31/08/2014.

Nota: Questa è una risposta automatizzata al messaggio "Re: - Background

color for pivot table cells with MISSING values" inviata il 8/18/14 11:21:48 AM.

Questa è l'unica notifica che verrà ricevuta mentre la persona è assente.

Re: Background color for pivot table cells with MISSING values

Thu, 18 Sep 2014 12:01:17 GMT

Hi,

I'm having the same issue. Trying to highlight the column with the current week number. The missing values do not get colored in the background.

THX for the help

Re: Background color for pivot table cells with MISSING values

Thu, 18 Sep 2014 12:21:00 GMT

You can replace the missing cell with 0. Go to presentation Tab in Chart properties.

Uncheck 'Suppress Zero-values

Uncheck Missing

Check populate Missing Cells

Specify the Missing Symbol and Null Symbol as 0(As shown)

I think it should work.

thanks.

Re: Background color for pivot table cells with MISSING values

Thu, 30 Oct 2014 10:30:29 GMT

Thank you but I have to use Suppress Zero-Values.

Thanks anyway.

Re: Background color for pivot table cells with MISSING values

Tue, 17 Mar 2015 16:42:25 GMT

I think I figured out a way to do this. If you uncheck 'Suppress Zero-values' , you will first get zeros in your cells with missing data. I needed dashes and not zeros ( but this will also work for spaces).

On the number tab under 'Format Pattern', I originally had #,##0;(#,##0) (in order to get parens around negative values). There is no final semicolon as you can see. To get the dashes, I added a semicolon after the (#,###0) and my dash, so the pattern looked like this:

#,##0;(#,##0);-

The background color then filled in all cells, including the ones with a dash. If you need spaces in the cells instead of dashes, you would put the semicolon alone without an additional character.

If you only have one pattern like this #,##0, it worked if I added two semicolons and then nothing if I wanted a space and a dash if i wanted a dash.