topic Re: Coloring cells according to a validation in QlikView

Coloring cells according to a validation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm generating a table where I group for a given service, several tasks. These tasks have different priority (critical, average and low) and I need to display a color map per service.

If any task is critical, turn it red. (regardless of whether you have medium or low tasks)

If any task is average, and has no critical, its yellow

If all tasks are low, they turn green.

Example:

Service	Task	Color
Service1	Task 1: Critical Task 2: Media Task 3: Media Task 4: Low	Red
Service 2	Taks 1: Low Task 2: Low Task 3: Media	Orange
Service 3	Task 1: Low Task 2: Low Task 3: Low	Green

Service1

Task 1: Critical

Task 2: Media

Task 3: Media

Task 4: Low

Red

Service 2

Taks 1: Low

Task 2: Low

Task 3: Media

Orange

Service 3

Task 1: Low

Task 2: Low

Task 3: Low

Green

thank you very much!

Re: Coloring cells according to a validation

sudeepkm — Thu, 21 Sep 2017 17:54:18 GMT

There must be a better way of implementing it but one way of doing it is to assign numbers per task status such as Critical as 3, Medium as 2 and Low as 1. In that way you can do an aggregation (max) on that number group by Service and then you can assign a color to that number.

In the script:

dat:

LOAD * Inline [

Service,Task,Status

1,1,low

1,2,low

1,3,medium

2,1,low

2,2,low

2,3,low

3,1,critical

3,2,medium

3,3,medium

3,4,low];

Join

LOAD * Inline [

Status,Num

low,1

medium,2

critical,3

];

// Each service will have a status and color

LOAD Service,if(max(Num) = 3,RGB(255,0,0),if(max(Num)=2,RGB(255,255,0),RGB(0,255,0))) as color Resident dat

Group by Service ;

Then in the UI:

Re: Coloring cells according to a validation

Anonymous — Thu, 21 Sep 2017 18:17:36 GMT

thank you,

The colors aren't based in if there are more critical or medium, or low, the rules are:

If exist, some critical, then the color is red.

if exist some medium and some low and any critical, then the color is yellow.

If exist only lows, the the color is green

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 19:50:57 GMT

Hi,

It works with paste the following formula as background expression color :

=if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 1"}>} Task),'critical')>0,lightred(),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Task),'media')>0

and SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Task),'critical')=0 ,rgb(255,156,57),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 3"}>} Task),'low')=3,lightgreen())))

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 20:00:37 GMT

Hi Paola,

For each service try this expression to set the colour:

Pick(Wildmatch(Concat(Task,'|'),'*Critical*','*Medium*','*Low*'),Red().Yellow(),Green())

Regards

Andrew

Re: Coloring cells according to a validation

Anonymous — Thu, 21 Sep 2017 20:45:26 GMT

Can you share me the document?.

Regards.

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 20:49:03 GMT

Of course, see attached .qvw

Regards

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 21:00:16 GMT

I have just surrender that you last line "If all tasks are low, they turn green." is not well taking in account in my formula because it was hard code with 3 rows.

So replace, the last line

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 3"}>} Task),'low')=3,lightgreen())))

with

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 3"}>} Task),'low')=NoOfRows(),lightgreen())))

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 21:15:11 GMT

Hi Paola,

You can get

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:low
Service 3	Task 2:low
Service 3	Task 3:low

With this background colour:

=Pick(Wildmatch(Concat(TOTAL <Service>Task,'|'),'*Critical*','*Medium*','*Low*'),LightRed(),Yellow(),LightGreen())

Cheers

Andrew

Re: Coloring cells according to a validation

Anonymous — Thu, 21 Sep 2017 21:21:12 GMT

mmm... i have the priority in other col:

Service	Task	Priority	Color
Service1	Task 1 Task 2 Task 3 Task 4	Critical Media Media Low	Red
Service 2	Taks 1 Task 2 Task 3	Low Low Media	Orange
Service 3	Task 1 Task 2 Task 3	Low Low Low	Green

Service1

Task 1

Task 2

Task 3

Task 4

Critical

Media

Low

Red

Service 2

Taks 1

Task 2

Task 3

Low

Media

Orange

Service 3

Task 1

Task 2

Task 3

Low

Green

your solutions is the same?

Regards.

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 21:24:18 GMT

But the problem with this formula is that for eg with Service 3, it will color green if any task is low whereas requirement for service 3 is "if all tasks are low".

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 21:30:31 GMT

Try with

=if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 1"}>} Priority),'critical')>0,lightred(),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Priority),'media')>0 and SubStringCount (concat(TOTAL <Service> {<Service={"Service 2"}>} Priority),'critical')=0 ,rgb(255,156,57),

if(SubStringCount (concat(TOTAL <Service> {<Service={"Service 3"}>} Priority),'low')=NoOfRows(),lightgreen())))

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 21:31:01 GMT

Hi Paola,

You can use

=Pick(Wildmatch(Concat(TOTAL <Service>Task,'|'),'*Critical*','*Medium*','*Low*'),RGB(255,0,0),RGB(255,165,0),RGB(0,255,0))

to give

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:low
Service 3	Task 2:low
Service 3	Task 3:low

Regards

Andrew

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 21:32:11 GMT

Not so:

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:low
Service 3	Task 2:low
Service 3	Task 3:critical

Re: Coloring cells according to a validation

Anonymous — Thu, 21 Sep 2017 21:39:32 GMT

Hi Jean,

Maybe I'm doing something wrong, I share my document, if you can review it,

Thank you.

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 21:39:34 GMT

Dear Jean-Baptiste,

Wildmatch(Concat(TOTAL <Service>Task,'|'),'*Critical*','*Medium*','*Low*')

will return 1 if 'Critical' is a substring of the concatenation of a Service's Tasks. If 'Critical' is not a substring it will return 2 if 'Medium' is a substring and if this is not then it will return 3 if 'Low' is a substring. If none of these is a substring 0 will be returned and the Pick function will not pick a colour.

Service	Task
Service 1	Task 1:critical
Service 1	Task 2:medium
Service 1	Task 3:medium
Service 1	Task 4:low
Service 2	Task 1:low
Service 2	Task 2:low
Service 2	Task 3:medium
Service 3	Task 1:hot
Service 3	Task 2:cold
Service 3	Task 3:cool

Kind regards

Andrew

Re: Coloring cells according to a validation

sergio0592 — Thu, 21 Sep 2017 21:40:11 GMT

Indeed, sorry

Re: Coloring cells according to a validation

effinty2112 — Thu, 21 Sep 2017 21:42:25 GMT

No apologies required! We're all just trying to help each other out.

Re: Coloring cells according to a validation

Anonymous — Thu, 21 Sep 2017 22:02:31 GMT

yeeiii... thank you so much!!

Its correct!!!!! but.. now I need put a other validation, whit status.

Green, if status is Closed/cerrada, or priority is low (like your example)
Orange and red, if status is "assigned" and rules applied.

I send you my document.

I really thank you.

Regards.