4 Replies Latest reply: Mar 20, 2012 7:01 AM by dupontpgy

# check value in pivot table

The data :

WARNINGS:

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:

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

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

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

• ###### check value in pivot table

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)

)

)

or

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

Sorry but both solutions do not work.