Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patriciousa
Creator II
Creator II

Color background expression when filtering by tables.

Hi Community.

I'm trying to build a "complex "color background expression.

I have these 2 tables in the same sheet. (The tables have more info.....)

DateDay

Extra Hours

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

WorkerExtra Hours
Patrick40
Leo10

When I pick the second Table, by Worker, in this case PATRICK. I get the following in the first table.

DateDay

Extra Hours

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

I need a color background expression that does the following:

If Saturday extra hours over 4, yellow, over 8 orange, over 10, red.

The thing is that the color expression should not apply until you pick a worker.

Is it possible?

Thank you in advance.

Regards.

1 Solution

Accepted Solutions
sunny_talwar

May be this:

if(GetSelectedCount(Worker)=1,

    If(sum[Extra Hours]>10,Red(),

          if(sum[Extra Hours]>8, rgb(255,128,0) ,

              if(sum[Extra Hours]>4, Yellow() ))))

View solution in original post

15 Replies
Gysbert_Wassenaar

if(GetSelectedCount(Worker)=1,

     If(sum[Extra Hours]>10,Yellow(),

          if(sum[Extra Hours]>8, rgb(255,128,0) ,

               if(sum[Extra Hours]>4, Red() ))))


talk is cheap, supply exceeds demand
patriciousa
Creator II
Creator II
Author

Hi Gysbert.

This is my syntaxis now: (Which Qlik says it's fine).

if(GetSelectedCount(Replace(Nombre, ',', ' '))=1,

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

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

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

However, it does not color any cell after doing the worker selection.

Thank you in advance.

Regards.

Gysbert_Wassenaar

I doubt that Replace(Nombre, ',', ' ') returns the name of a field. Try just Nombre instead


talk is cheap, supply exceeds demand
patriciousa
Creator II
Creator II
Author

You are right. It does work with just "Nombre".

One more thing I need to add. The color expression should only exist when the day is Saturday or Sunday.

Thank you very much Gysbert.

Kind regards.

patriciousa
Creator II
Creator II
Author

Issue #2. My date is done by this calendar and I'm using WeekDay.

Calendar:
DECLARE FIELD DEFINITION TAGGED '$date'
Parameters
first_month_of_year = 1
Fields
Year($1) As Year Tagged '$year',
Month($1) as Month Tagged '$month',
Num(Month($1)) as MonthNum Tagged '$month',
Date($1) as Date Tagged ('$date', '$day'),
Week($1) as Week Tagged '$week',
Weekday($1) as Weekday Tagged '$weekday',
DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS "Fecha Registro" USING Calendar;

Thank you again.

Regards.

patriciousa
Creator II
Creator II
Author

I tried the following but it doesn't give me any color result. The syntaxis is OK.

if(GetSelectedCount(Nombre)=1,

     If(Sum({<"NombreHoraExtra"={"Hora Extra 100"},"Fecha Registro"={"Sábado", "Domingo"}>} Cantidad)>4,Yellow(),

          if(Sum({<"NombreHoraExtra"={"Hora Extra 100"},"Fecha Registro"={"Sábado", "Domingo"}>} Cantidad)>8, rgb(255,128,0) ,

               if(Sum({<"NombreHoraExtra"={"Hora Extra 100"},"Fecha Registro"={"Sábado", "Domingo"}>} Cantidad)>10, Red() ))))

Being Sábado and Domingo, Saturday and Sunday in spanish.

Thank you.

Regards.

patriciousa
Creator II
Creator II
Author

I just realise it won't make any effect because the day is affecting the SUM and it has nothing to do with it....

So... How do I do it?

Thank you haha.

patriciousa
Creator II
Creator II
Author

Forget about everything I have posted after the Name thing, I got it fixed .

ONE MORE QUESTION!

You know, your filter is actually painting everything in yellow. It doesn't not matter if they have 4, 6 or >10.

Everything above 4 is being painted yellow.

Thank you Gysbert!

sunny_talwar

May be this:

if(GetSelectedCount(Worker)=1,

    If(sum[Extra Hours]>10,Red(),

          if(sum[Extra Hours]>8, rgb(255,128,0) ,

              if(sum[Extra Hours]>4, Yellow() ))))