Cell colors overwrite in Pivot

[Anonymous]

Hi All

I need color to overwrite with below condition

All row color should follow the pattern Green -> Amber -> Red -> Black

For above example I need as below

Zone  MB1  MB2  MB3  MB4

East   G       B       B       B   --> Amber should turn to Black for MB3 & MB4

West  R       R       R       R  --> Amber & Green should turn Red because pattern Green Amber Red Black

North  B       B       B       B   --> All blacks

South A       R       R       R   --> After Red no Amber allowed for MB3 & MB4

I have attached TestRAG.QVW. For back ground color I have created variables

Thanks in advance for your valuable suggestion

[vvira1316 · Accepted solution]

Hi Satish,

If I'm understanding your logic right than shouldn't North MB1 should be Red but MB2, MB3, and MB4 be black? What you want is MB1 should use

Green for 1, Amber for 2, Red for 3 and Black for 4

after that MB2 should look for value of MB1, if that value (MB2) is lower than MB1 then it should be same as MB1 color

MB3 should compare MB1 and MB2 before using it's own number for color selection.

Similarly for MB4 should compare MB1, MB2 and MB3 before using it's own number for color selection

In your logic you are just using its own individual value hence it is not working for you.

Please clarify and we can come up with logic to get it working.

BR,

Vijay

[Anonymous]

Hi Vijay

This solution does not over write color as requirents

Thanks for your valuable time

[vvira1316]

Hi Satish,

I'm not trying to change the requirement but in the information you have provided the ordering of Zone in image and your explanation are not in same order/correspond to different zones hence I was seeking clarification

Zones in Picture

East

North

South

West

Zone  MB1  MB2  MB3  MB4

East   G       B       B       B   --> Amber should turn to Black for MB3 & MB4 (East in image 1,4,2,2)

West  R       R       R       R  --> Amber & Green should turn Red because pattern Green Amber Red Black (North in image, 3,2,3,1)

North  B       B       B       B   --> All blacks (It sounds that this corresponds to South in image 4,1,3,4)

South A       R       R       R   --> After Red no Amber allowed for MB3 & MB4 (It sounds that this corresponds to West in image 2,3,2,2)

[pradosh_thakur]

Hi

i guess you are not exactly clear with your requirement.

regards

Pradosh

[Anonymous]

see if that matches

[Anonymous]

My requirements is straightforward.

All color must be in sequence of Green-Amber-Red-Black if all exist

If Green after Amber should be changed to Amber until hit Red color or upto end

If Green or Amber after Red should be change to Red until hit Black or until end

Thanks

[vvira1316]

Hi,

I've done for MB2.

=If(Max({<Zone>}B2) >= Max({<Zone>}B1),
If(Max({<Zone>}B2)=1, Green(),
If(Max({<Zone>}B2)=2, RGB(255,128,0),
If(Max({<Zone>}B2)=3, Red(),Black()
))),
If(Max({<Zone>}B1)=1, Green(),
If(Max({<Zone>}B1)=2, RGB(255,128,0),
If(Max({<Zone>}B1)=3, Red(),Black()
)))
)

For MB3 you will have to similarly check/compare MB2 and MB1 with MB3 and if MB3 is less than either one of them then use the MB2 or MB1 whichever is higher.

[vvira1316]

here it is for MB3

=If(Max({<Zone>}B2) <= Max({<Zone>}B3),
If(Max({<Zone>}B1) <= Max({<Zone>}B3),
If(Max({<Zone>}B3)=1, Green(),
If(Max({<Zone>}B3)=2, RGB(255,128,0),
If(Max({<Zone>}B3)=3, Red(),Black()
))),
If(Max({<Zone>}B1) > Max({<Zone>}B3),
If(Max({<Zone>}B1)=1, Green(),
If(Max({<Zone>}B1)=2, RGB(255,128,0),
If(Max({<Zone>}B1)=3, Red(),Black()
))))),
If(Max({<Zone>}B2) <= Max({<Zone>}B3),
If(Max({<Zone>}B3)=1, Green(),
If(Max({<Zone>}B3)=2, RGB(255,128,0),
If(Max({<Zone>}B3)=3, Red(),Black()
))),
If(Max({<Zone>}B2)=1, Green(),
If(Max({<Zone>}B2)=2, RGB(255,128,0),
If(Max({<Zone>}B2)=3, Red(),Black()
)))
))

[vvira1316]

Here it is for all of them

=If(Max({<Zone>}B3) <= Max({<Zone>}B4),
If(Max({<Zone>}B2) <= Max({<Zone>}B4),
If(Max({<Zone>}B1) <= Max({<Zone>}B4),
If(Max({<Zone>}B4)=1, Green(),
If(Max({<Zone>}B4)=2, RGB(255,128,0),
If(Max({<Zone>}B4)=3, Red(),Black()
))),
If(Max({<Zone>}B1)=1, Green(),
If(Max({<Zone>}B1)=2, RGB(255,128,0),
If(Max({<Zone>}B1)=3, Red(),Black()
)))),
If(Max({<Zone>}B2) > Max({<Zone>}B4),
If(Max({<Zone>}B2)=1, Green(),
If(Max({<Zone>}B2)=2, RGB(255,128,0),
If(Max({<Zone>}B2)=3, Red(),Black()
))),
If(Max({<Zone>}B4)=1, Green(),
If(Max({<Zone>}B4)=2, RGB(255,128,0),
If(Max({<Zone>}B4)=3, Red(),Black()
))))),
If(Max({<Zone>}B3) > Max({<Zone>}B4),
If(Max({<Zone>}B3)=1, Green(),
If(Max({<Zone>}B3)=2, RGB(255,128,0),
If(Max({<Zone>}B3)=3, Red(),Black()
))),
If(Max({<Zone>}B4)=1, Green(),
If(Max({<Zone>}B4)=2, RGB(255,128,0),
If(Max({<Zone>}B4)=3, Red(),Black()
)))))