Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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())))
Just for Patrick? How you decide that one of the Worker need to have a color?
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.
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()))))
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.
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())))))
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?
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())))
Why are you soooo awesome? It's not fair... really.
It worked!
Thank you very very much.
Best regards!