Background color expression - 2 tables condition

[patriciousa]

Hi Community.

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

Date	Day	Extra Hours
20/02/2016	Saturday	80
20/03/2016	Sunday	10
17/01/2016	Sunday	50

Worker	Extra Hours
Patrick	40
Leo	10

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

Date	Day	Extra Hours
20/02/2016	Saturday	12
20/03/2016	Saturday	8
17/01/2016	Saturday	4

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.

Worker	Extra Hours
Patrick	40
Leo	10

Thank you in advance.

Regards.

[sunny_talwar]

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

[patriciousa]

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.

[sunny_talwar]

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]

Hi Sunny.

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

One single sheet containing 2 tables.

Table A:

Worker	Extra Hours
Patrick	80
Leo	12
Sunny	80

Table B

Date "(=Date([Fecha Registro]))"	Extra hours
02/03/2016	9
02/04/2016	40
02/05/2016	180

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/2016	5
02/04/2016	5
02/05/2016	14

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/2016	8
02/04/2016	4
02/05/2016	4

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/2016	0
02/04/2016	0
02/05/2016	12

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:

Worker	Extra Hours
Patrick	80 (Cell in Red)
Leo	12 (Cell in red)
Sunny	80 (Cell in Orange)

And this is the list of colors:

>12,Red(),

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

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

Thank you.

Regards.

[sunny_talwar]

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]

    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?

[sunny_talwar · Accepted solution]

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]

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

It worked!

Thank you very very much.

Best regards!