Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Sorry but both solutions do not work.
Thank you for your propositions.
Pierre