topic Pivot Table - Expression Background Color in QlikView

Pivot Table - Expression Background Color

Tue, 10 Nov 2009 12:27:23 GMT

When a custom background color (heat map) is created for an expression in a pivot table the background color for the first row of data is duplicated for the expression header. This does not seem right, is there a way to hide the header row for the expression. I have 2 expressions for the pivot and it is adding the header row for both and there is nothing in the presentation tab that will allow me to suppress the headers and/or not create a background color for these.

Any ideas?

Pivot Table - Expression Background Color

Tue, 10 Nov 2009 22:10:34 GMT

I tried rowno() function and found that rowno() = 1 automatically includes not only the first row but also the header row - unfortunately, that explains why it is painting the background color of the row header with the first data row. This would work if this behavior is changed so that we can hide the header by using rowno() = 0 assuming 0 is the index assigned for the header row. Is this a bug or a feature?

Pivot Table - Expression Background Color

Miguel_Angel_Baeyens — Wed, 11 Nov 2009 22:56:00 GMT

Hello,

I'm sure this one is the same problem as yours. The merit is for sebagiar, not mine, though!

Pivot Table - Expression Background Color

Wed, 11 Nov 2009 23:22:54 GMT

You should try using dimensionality(), when it is zero then that would be the header row (or column).

Pivot Table - Expression Background Color

Wed, 11 Nov 2009 23:50:08 GMT

Sorry, I forgot... write it on BACKGROUDN property inside de object.

Pivot Table - Expression Background Color

Thu, 12 Nov 2009 20:42:56 GMT

dimensionality() = 0 will not work, according to the documentation - 0 is for grand total cell and not the header. The only other workaround I was hoping for was to 'Custom Format Cell' using the active grid. However it seems like if you have a background color set via the chart expression you cannot override it by custom formatting the cell. Not sure if there are any other workarounds to suppress the header row.....

Pivot Table - Expression Background Color

Miguel_Angel_Baeyens — Thu, 12 Nov 2009 22:47:21 GMT

If you just don't want to see the header row, go to Chart properties, Presentation tab and check "Supress Header Row" (or something similar, I'm using QV 9.00 SR1 in Spanish, so I've translated the checkbox label from Spanish).

Pivot Table - Expression Background Color

Thu, 12 Nov 2009 23:09:42 GMT

Suppress Header Row is not available for Pivot Tables, I cant find that check box on the presentation tab. I believe it only exists for Straight Tables.

Pivot Table - Expression Background Color

Sat, 14 Nov 2009 03:08:40 GMT

Another try: If the header row is a dimention (like month-like) you could change that colour from the dimentions tab.

Pivot Table - Expression Background Color

Miguel_Angel_Baeyens — Mon, 16 Nov 2009 16:10:30 GMT

Maybe it's a poor approach, but I'm still trying to find out how to do it and what I'm using in some cases is

=''

As Label, or even =Null() depending on how do you deal with Null values (dash sign, nothing...). By the way you are right, that checkbox exists only for straight tables, not pivot tables.

AW:Pivot Table - Expression Background Color

Tue, 22 Feb 2011 14:21:21 GMT

If you use "Visual Cues" for the expressions and try to make your background color (heat map) in the "Normal" / Background definition, then the color is not duplicated in the expression header.

If you want to define a color for the header itself, you can use the "Text" / Background definition.

Worked fine with a similar problem for us.

HTH

Pivot Table - Expression Background Color

Thu, 23 Feb 2012 15:42:22 GMT

HTH,

That's a good solution but is only works if you want to evaluate the same expression in "Expression" tab and in "Visual Cues" tab. If you need to vary expression line color by comparing other fields' values, "Visual Cues" doesn't do the trick.

Has anyone been able to solve this?

Re: Pivot Table - Expression Background Color

leocvaz — Fri, 23 Nov 2012 13:02:46 GMT

Hi,

I had the same problem. I made a workaround that worked in my case.

- In presentation put the sub total on top

- in the backgroud color of the expression test the dimensionality (do not color the subtotal)

The subtotal row works like a barrier for the colored cells.

bye

Re: Pivot Table - Expression Background Color

Sun, 07 Jul 2013 14:58:33 GMT

HTH, your response was very useful for me.

I thnk that you can change the color of the header by doing waht HTH says using calculated color, and inside this option you can use different ifs to set the different colors. I have not tried this solution but I think it is going to work.

Regards,

Marcelo