topic Background color expression - 2 tables condition in App Development

Background color expression - 2 tables condition

patriciousa — Wed, 13 Apr 2016 18:56:41 GMT

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.

Re: Background color expression - 2 tables condition

sunny_talwar — Wed, 13 Apr 2016 19:06:32 GMT

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

Re: Background color expression - 2 tables condition

patriciousa — Wed, 13 Apr 2016 19:12:09 GMT

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.

Re: Background color expression - 2 tables condition

sunny_talwar — Wed, 13 Apr 2016 19:35:08 GMT

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

Re: Background color expression - 2 tables condition

patriciousa — Thu, 14 Apr 2016 14:21:38 GMT

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.

Re: Background color expression - 2 tables condition

sunny_talwar — Thu, 14 Apr 2016 14:30:16 GMT

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

Re: Background color expression - 2 tables condition

patriciousa — Thu, 14 Apr 2016 14:51:27 GMT

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?

Re: Background color expression - 2 tables condition

sunny_talwar — Thu, 14 Apr 2016 14:54:51 GMT

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

Re: Background color expression - 2 tables condition

patriciousa — Thu, 14 Apr 2016 15:05:54 GMT

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

It worked!

Thank you very very much.

Best regards!