check value in pivot table

[#1]

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

[CELAMBARASAN]

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

[#3]

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

[CELAMBARASAN]

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

[#5]

Sorry but both solutions do not work.

Thank you for your propositions.

Pierre