Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

satishkamath
Contributor II

Cell colors overwrite in Pivot

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

1 Solution

Accepted Solutions
vvira1316
Valued Contributor II

Re: Cell colors overwrite in Pivot

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

16 Replies
vvira1316
Valued Contributor II

Re: Cell colors overwrite in Pivot

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

satishkamath
Contributor II

Re: Cell colors overwrite in Pivot

Hi Vijay

This solution does not over write color as requirents

Thanks for your valuable time

vvira1316
Valued Contributor II

Re: Cell colors overwrite in Pivot

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

RAG.png

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
Valued Contributor III

Re: Cell colors overwrite in Pivot

Hi

i guess you are not exactly clear with your requirement.

regards

Pradosh

psilva001
Contributor

Re: Cell colors overwrite in Pivot

see if that matches

satishkamath
Contributor II

Re: Cell colors overwrite in Pivot

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
Valued Contributor II

Re: Cell colors overwrite in Pivot

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
Valued Contributor II

Re: Cell colors overwrite in Pivot

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()
)))
))

Color.PNG

vvira1316
Valued Contributor II

Re: Cell colors overwrite in Pivot

Color2.PNG

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()
)))))