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

    check value in pivot table

      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 Adhimulam

          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