Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

patriciousa
Contributor

Background color expression - 2 tables condition

Hi Community.

I have the following 2 tables on a sheet in Qlik Sense.

DateDay

Extra Hours

20/02/2016Saturday80
20/03/2016Sunday10
17/01/2016Sunday50

WorkerExtra Hours
Patrick40
Leo10

Whe I pick Patrick I get the following thanks to:

(f(GetSelectedCount(Nombre)=1,

     If(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>12,Red(),

          if(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>8, rgb(255,128,0) ,

               if(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>4, Yellow(),
                              if(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)<=4, Green(),)))))

DateDay

Extra Hours

20/02/2016Saturday12
20/03/2016Saturday8
17/01/2016Saturday4

What I Need

Withouth making any filter, I need the Extra Hours of Patrick in this chart to be RED so I know that I have to check Patrick's hours because he has extra hours over 12 in a single day.

WorkerExtra Hours
Patrick40
Leo10

Thank you in advance.

Regards.

1 Solution

Accepted Solutions
MVP
MVP

Re: Background color expression - 2 tables condition

You removed GetselectedCount() if statement? Does Nombre field refers to the name of the work? If it does, may be this:

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >12, Red(),

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >8, RGB(255,128,0) ,

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >4, Yellow(),
If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) <=4, Green())))

8 Replies
MVP
MVP

Re: Background color expression - 2 tables condition

Just for Patrick? How you decide that one of the Worker need to have a color?

patriciousa
Contributor

Re: Background color expression - 2 tables condition

It must apply to every worker. I just put Patrick as example because I know Patrick has more than 12 hours in a single day but his total of extra hours is 40. That 40 should be red because he has over 12 extra hours in a day.

Did I make myself clear?

Maybe Leo has only 5 extra hours, so he should be yellow.

Etc.

Thank you Sunny.

Regards.

MVP
MVP

Re: Background color expression - 2 tables condition

May be this:

If(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>12,Red(),

If(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>8, RGB(255,128,0) ,

If(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)>4, Yellow(),
If(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad)<=4, Green()))))

patriciousa
Contributor

Re: Background color expression - 2 tables condition

Hi Sunny.

That won't work. I will give you a much more detailed explanation:

One single sheet containing 2 tables.

Table A:

WorkerExtra Hours
Patrick80
Leo12
Sunny80

Table B

Date "(=Date([Fecha Registro]))"Extra hours
02/03/20169
02/04/201640
02/05/2016180

Now take a look to these 3 examples:

1. I pick Patrick in table A and I get the following in table B:

Date "(=Date([Fecha Registro]))"Extra hours
02/03/20165
02/04/20165
02/05/201614

Patrick has exceeded 12 hours in a single day, so the 80 hours in table A for Patrick must be RED.

2. I pick Sunny in the table A and I get the following in table B

Date "(=Date([Fecha Registro]))"Extra hours
02/03/20168
02/04/20164
02/05/20164

Sunny has a total of 80 hours like Patrick, but he never exceeded 12 hours in a single day, so his 80 hours in Table A must be ORANGE:

3. I pick Leo in the table A and I get the following in Table B.

Date "(=Date([Fecha Registro]))"Extra hours
02/03/20160
02/04/20160
02/05/201612

Leo has only a total of 12 hours but he used all of them in a single day, exceeding the daily limit, therefore, Leo hours in table A must be RED like Patrick's hours.

Final Form of table A:

WorkerExtra Hours
Patrick80 (Cell in Red)
Leo12 (Cell in red)
Sunny80 (Cell in Orange)

And this is the list of colors:

>12,Red(),

>8, RGB(255,128,0) ,

>4, Yellow(),
<=4, Green()

Thank you.

Regards.

MVP
MVP

Re: Background color expression - 2 tables condition

Try this out:

(If(GetSelectedCount(Nombre) = 1,

    If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >12,Red(),

          if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >8, rgb(255,128,0) ,

              if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >4, Yellow(),
                              if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) <=4, Green())))))

patriciousa
Contributor

Re: Background color expression - 2 tables condition

    If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >12,Red(),

          if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >8, rgb(255,128,0) ,

              if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) >4, Yellow(),
                              if(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date)) <=4, Green())))

This seems to be it! or at least is like 80% close.

It is "partially working". It's only painting half of the fields and some of them are not even correctly... Like this guy exceeds 12 hours and is red , but another is red but he doesn't even have extra hours!!

What is the problem?

MVP
MVP

Re: Background color expression - 2 tables condition

You removed GetselectedCount() if statement? Does Nombre field refers to the name of the work? If it does, may be this:

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >12, Red(),

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >8, RGB(255,128,0) ,

If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) >4, Yellow(),
If(Max(Aggr(Sum({<"NombreHoraExtra"={"Hora Extra 100"}>} Cantidad), Date, Nombre)) <=4, Green())))

patriciousa
Contributor

Re: Background color expression - 2 tables condition

Why are you soooo awesome? It's not fair... really.

It worked!

Thank you very very much.

Best regards!