8 Replies Latest reply: Apr 14, 2016 11:05 AM by patrico mesri RSS

    Background color expression - 2 tables condition

    patrico mesri

      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.

        • Re: Background color expression - 2 tables condition
          Sunny Talwar

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

            • Re: Background color expression - 2 tables condition
              patrico mesri

              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

                  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
                      patrico mesri

                      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.

                        • Re: Background color expression - 2 tables condition
                          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())))))

                            • Re: Background color expression - 2 tables condition
                              patrico mesri

                               

                                  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?