topic check value in pivot table in QlikView

check value in pivot table

Tue, 20 Mar 2012 08:29:18 GMT

The data :

WARNINGS:

Load * inline [

WProduct, WSite, WLevel0, WLevel1

P1, S1, 0.05,0.3

P2, S1, 0.1, 0.5

P3, S2, 0.3, 0.7

P2, S3, 0.04, 0.08

P1, S4, 0.09, 0.8

];

OUTOFSPEC:

Load * inline [

Product, Site, Ratio

P1, S1, 0.1

P1, S2, 0.1

P4, S4, 0.8

P3, S4, 0.5

];

The Pivot Table

Site Product P1 P3 P4

S1 0.1 - -

S2 0.1 - -

S4 - 0.5 0.8

I want to color the bakground cell.

If, for example, I write :

IF(Ratio < (IF(Site='S1' AND Product='P1', ONLY({$<WSite={'S1'}, WProduct={'P1'} >} WLevel0)))

,RGB(255,0,0)

,IF(Ratio < (IF(Site='S1' AND Product='P1', ONLY({$<WSite={'S1'}, WProduct={'P1'} >} WLevel1)))

,RGB(255,255,0))

)

It is what I want to have but I Have to do that for all the combinations of values of Site, Product.

So I Try :

IF(Ratio < ONLY({$<WSite=Site, WProduct=Product >} WLevel0)

,RGB(255,0,0)

,IF(Ratio < ONLY({$<WSite=Site, WProduct=Product >} WLevel1)

,RGB(255,255,0)

)

But it fails.

I suspect the condition "WSite=Site, WProduct=Product" to be wrong.

What should I write to make it work ?

Pierre

PS : For the solution, I don't want to link the tables WARNINGS and OUTOFSPEC

check value in pivot table

CELAMBARASAN — Tue, 20 Mar 2012 09:16:55 GMT

Hi,

Link Products and Out of spec table

Load *,WProduct & '_' & WSite as Key inline [

WProduct, WSite, WLevel0, WLevel1

P1, S1, 0.05,0.3

P2, S1, 0.1, 0.5

P3, S2, 0.3, 0.7

P2, S3, 0.04, 0.08

P1, S4, 0.09, 0.8

];

OUTOFSPEC:

Load *,Product & '_' & Site as Key inline [

Product, Site, Ratio

P1, S1, 0.1

P1, S2, 0.1

P4, S4, 0.8

P3, S4, 0.5

];

Then use expression as

if(Ratio <WLevel0,

RGB(255,0,0),

if(Ratio<WLevel1,

RGB(255,255,0)))

Celambarasan

check value in pivot table

Tue, 20 Mar 2012 10:01:13 GMT

Thank you

But you didn't read the Post Scriptum : " I don't want to link the tables WARNINGS and OUTOFSPEC". It's too easy. My example is a simplification of a more complicated case, a simplification to have an answer to that general problem.

Pietrre

Celambarasan Adhimulam

check value in pivot table

CELAMBARASAN — Tue, 20 Mar 2012 10:31:24 GMT

Hi,

To go in your way check with this

IF(Ratio < ONLY({$<WSite=P(Site), WProduct=P(Product)>} WLevel0)

,RGB(255,0,0)

,IF(Ratio < ONLY({$<WSite=P(Site), WProduct=P(Product)>} WLevel1)

,RGB(255,255,0)

)

IF(Ratio < Sum(Aggr(if(WSite=Site and WProduct=Product,WLevel0),WSite,WProduct))

,RGB(255,0,0)

,IF(Ratio < Sum(Aggr(if(WSite=Site and WProduct=Product,WLevel1),WSite,WProduct))

,RGB(255,255,0)

)

Celambarasan

check value in pivot table

Tue, 20 Mar 2012 11:01:30 GMT

Sorry but both solutions do not work.

Thank you for your propositions.

Pierre