topic Background Color Pivot Expression Exclude Header in QlikView

Background Color Pivot Expression Exclude Header

denwo2010 — Wed, 17 Nov 2010 16:39:09 GMT

Hi,

I have a pivot and have the following for my Background Color

If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0)))

It works but it is Highlighting the Header which I do want how do I exclude this from happening?

Thanks

Background Color Pivot Expression Exclude Header

Wed, 17 Nov 2010 16:54:53 GMT

Hi,

try to use the dimensionality( ) function in your if-clause like if (dimensionality ( )= 0 and .....)

Regards, Roland

Background Color Pivot Expression Exclude Header

denwo2010 — Wed, 17 Nov 2010 16:58:24 GMT

What would the Syntax be for this now?

Thanks

Background Color Pivot Expression Exclude Header

Wed, 17 Nov 2010 17:21:02 GMT

Hi.

The function dimensionality() returns the level for every line in your pivot or straight table. An easy way to check the return-value is an expression only with that function.

So add to every if-condition this function: If([SLA]<[SLA Target] and dimensionality()<> 0, RGB())... If(IsNull([SLA]) and dimensionality()<> 0, ....... should work as you want it.

Background Color Pivot Expression Exclude Header

denwo2010 — Wed, 17 Nov 2010 17:36:51 GMT

Hi, Does not seem to have worked header is still being Highlighted

Background Color Pivot Expression Exclude Header

boorgura — Wed, 17 Nov 2010 17:39:19 GMT

We have discussed this earlier.

And the only work around is to have the subtotal on the top and the including the condition:

if(rowno() <>0, <color_expression>)

Background Color Pivot Expression Exclude Header

boorgura — Wed, 17 Nov 2010 17:39:48 GMT

And please do let me know if there is a better and straight way to do it. 🙂

Background Color Pivot Expression Exclude Header

denwo2010 — Wed, 17 Nov 2010 17:51:40 GMT

What I need to change the below to?

If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0)))

Thanks

Background Color Pivot Expression Exclude Header

Wed, 17 Nov 2010 18:00:42 GMT

Hi again,

sorry, the cardinality() works NOT for pivot tables. Straight tables are ok. Sorry. So with the workaround from Rocky it should be:


If([SLA]<[SLA Target] and rowno() <>0, RGB(255,0,0),
 If(IsNull([SLA]) and rowno() <>0, RGB(214,231,247), RGB(0,255,0)))

Background Color Pivot Expression Exclude Header

boorgura — Wed, 17 Nov 2010 19:56:52 GMT

After you add subtotals (that is check the partial sum option for the first dimension.

Change the expression as:

if(rowno()<>0,
If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0))))

This should do...

Background Color Pivot Expression Exclude Header

sparur — Fri, 19 Nov 2010 13:11:27 GMT

Hello, Rocky.

I have the same problem with a pivot table.

Your suggestion is work for Total line only. but not work for header 😞

see in my small example.

P.S. in Straight table - No problem

Background Color Pivot Expression Exclude Header

boorgura — Fri, 19 Nov 2010 18:41:23 GMT

If you have a requirement to have the subtotal at the bottom, I'm afraid you cannot solve this.

If that is not a requirement - just choose the option to have the subtotals at top in the Presentation tab.

For some reason I'm not able to attach the file.

Background Color Pivot Expression Exclude Header

ivandrago — Fri, 19 Nov 2010 18:46:22 GMT

The Total Mode is greyed out so how would you be able to move this to the top?

Background Color Pivot Expression Exclude Header

boorgura — Fri, 19 Nov 2010 18:57:32 GMT

In the Presentation TAB in the settings - set the Subtotals to be at TOP.

Background Color Pivot Expression Exclude Header

sparur — Fri, 19 Nov 2010 19:06:18 GMT

Rocky, thanks

but unfortunately I don't need in total line, so your suggestion doesn't solve my problem 😞

Background Color Pivot Expression Exclude Header

boorgura — Fri, 19 Nov 2010 19:57:53 GMT

Sorry - dint get you.

Do you mean - you should not have Total Line or should the Total Line be at the bottom??

Background Color Pivot Expression Exclude Header

sparur — Fri, 19 Nov 2010 20:53:55 GMT

I shouldn't have Total line in my pivot table

Background Color Pivot Expression Exclude Header

boorgura — Fri, 19 Nov 2010 21:44:48 GMT

Hmm... you have the same exact problem I do.

Check this one. this is what I use.

This is by custom formatting the total cells with background color and text color being the same.

Background Color Pivot Expression Exclude Header

sparur — Sat, 20 Nov 2010 15:23:09 GMT

Rocky, Thank you for your response.

but you don't understand my problem... please see in my qvw-example (I uploaded it some earlier). I have pivot table where my label of expression "Value" has green background. I don't want it. I don't need total line( check off "Show partial sums" for all my dimensions in presentation tab) and I don't want green header for my expression.

Background Color Pivot Expression Exclude Header

amien — Wed, 27 Jul 2011 10:18:07 GMT

Sparur, how did you solved this? having the same issue