topic Background color in pivot in QlikView

Background color in pivot

Wed, 30 Jun 2010 07:47:49 GMT

Hi,

My case is different from normal coloring of cell background.

Account wise avg is 5. If individual Eng's avg is less than Account's Avg, background will be Red, else Green.

Its perfect till no selection made.

No Selection
Account	Eng	Month	Avg
			5
Acct A	Eng A	Feb-10	4
Acct A	Eng A	Mar-10	4
Acct A	Eng B	Feb-10	6
Acct A	Eng B	Mar-10	6

Eng A selected
Account	Eng	Month	Avg
			4
Acct A	Eng A	Feb-10	4
Acct A	Eng A	Mar-10	4

If I select a engineer, eg I selected Eng A above, then Account's avg will also be 4...Now Eng A is in Green Background. I still want it in Red ie it should be compared to Account A's avg 5.

Any Suggestion!!

AW:Background color in pivot

brenner_martina — Wed, 30 Jun 2010 07:57:41 GMT

Hi, Arun,

can you please upload an example or tell use the expression for the background color

AW:Background color in pivot

Wed, 30 Jun 2010 08:04:55 GMT

Hi Martina,

M using below expression

If(Avg(Value) < Avg(Total <Account, Month> Value),RGB(255,0,0), RGB(0,255,0))

AW:Re: AW:Background color in pivot

brenner_martina — Wed, 30 Jun 2010 08:12:34 GMT

Hi, Arun,

you have to change your expression, so that you have only the first dimension in the Total-expression, p.e.

If(Avg(Value) < Avg(Total <Month> Value) )
or
If(Avg(Value) < Avg(Total <Account> Value) )

AW:Background color in pivot

sparur — Wed, 30 Jun 2010 08:14:24 GMT

Hello, arun,

strange, but I check you situation and it worked perfectly.

When I select Eng A background is changed into Green

AW:Background color in pivot

Wed, 30 Jun 2010 08:15:17 GMT

Hi Arun,

You should use AGGR function... what you want is (at least I think so;):

if (Avg(Value) < aggr(avg(Value), Account, Month), RGB(255,0,0), RGB(0,255,0))

Is Eng also a dimension - should you also include it in the aggregation dimension ?

If you could post you app here, we could help you 😉

Regards,

Miha

AW:Background color in pivot

Wed, 30 Jun 2010 11:08:34 GMT

Dear All,

All suggestions are not working.

It is a requirement where we need to put set analysis i suppose. Such that Avg will everytime be 5 only regardless of any selection.

Wud like to clear one thing here. Selection for Engineer should only be neglected. Because I can select a particular department for a account. Then all engineers' avg will be compared with the avg for that department only.

AW:Background color in pivot

sparur — Wed, 30 Jun 2010 11:27:55 GMT

can you post your qvw?

AW:Background color in pivot

Wed, 30 Jun 2010 11:56:37 GMT

attached is the qvw. I need Eng A in red only.

AW:Background color in pivot

sparur — Wed, 30 Jun 2010 12:04:32 GMT

may be like this...