topic Re: Cell colors overwrite in Pivot in QlikView

Cell colors overwrite in Pivot

Anonymous — Mon, 02 Oct 2017 21:42:34 GMT

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

Re: Cell colors overwrite in Pivot

vvira1316 — Mon, 02 Oct 2017 22:44:52 GMT

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

Re: Cell colors overwrite in Pivot

Anonymous — Tue, 03 Oct 2017 21:11:18 GMT

Hi Vijay

This solution does not over write color as requirents

Thanks for your valuable time

Re: Cell colors overwrite in Pivot

vvira1316 — Wed, 04 Oct 2017 11:53:03 GMT

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)

Re: Cell colors overwrite in Pivot

pradosh_thakur — Wed, 04 Oct 2017 16:21:40 GMT

i guess you are not exactly clear with your requirement.

regards

Pradosh

Re: Cell colors overwrite in Pivot

Anonymous — Wed, 04 Oct 2017 17:13:54 GMT

see if that matches

Re: Cell colors overwrite in Pivot

Anonymous — Wed, 04 Oct 2017 23:30:29 GMT

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

Re: Cell colors overwrite in Pivot

vvira1316 — Thu, 05 Oct 2017 14:57:14 GMT

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.

Re: Cell colors overwrite in Pivot

vvira1316 — Thu, 05 Oct 2017 15:25:58 GMT

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

Re: Cell colors overwrite in Pivot

vvira1316 — Thu, 05 Oct 2017 16:10:33 GMT

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

Re: Cell colors overwrite in Pivot

Anonymous — Thu, 05 Oct 2017 23:33:40 GMT

Hi Vijay

It works, Thanks for your efforts

Thanks again

Re: Cell colors overwrite in Pivot

Anonymous — Thu, 05 Oct 2017 23:33:52 GMT

Thanks Vijay

Re: Cell colors overwrite in Pivot

vvira1316 — Thu, 05 Oct 2017 23:38:40 GMT

You are welcome. I'm glad I can help. Please close the thread. If you got the solution you needed.

BR,

Vijay

Re: Cell colors overwrite in Pivot

vvira1316 — Tue, 10 Oct 2017 18:39:11 GMT

Hi Satish,

If you have found the answer to this question then please close the thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Cell colors overwrite in Pivot

vvira1316 — Wed, 11 Oct 2017 18:24:55 GMT

Hi Satish,

I appreciate you marking answer as Helpful and responding to requests but what I really wanted you to do was select an answer if there is any that is a correct solution to your question.

If there is none but you have found a solution then you can just close it by marking your question "assumed answered"

BR,

Vijay

Re: Cell colors overwrite in Pivot

Anonymous — Wed, 11 Oct 2017 19:47:25 GMT

How do I close?

Re: Cell colors overwrite in Pivot

vvira1316 — Wed, 11 Oct 2017 20:34:09 GMT

Hi,

If someone replied to you then you will see following Correct Answer option. You just select that and it will mark your post answered.