Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

patriciousa
Contributor

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
MVP
MVP

Re: Color background expression when filtering by tables.

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

15 Replies

Re: Color background expression when filtering by tables.

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
Contributor

Re: Color background expression when filtering by tables.

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.

Re: Color background expression when filtering by tables.

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


talk is cheap, supply exceeds demand
patriciousa
Contributor

Re: Color background expression when filtering by tables.

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
Contributor

Re: Color background expression when filtering by tables.

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
Contributor

Re: Color background expression when filtering by tables.

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
Contributor

Re: Color background expression when filtering by tables.

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
Contributor

Re: Color background expression when filtering by tables.

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!

MVP
MVP

Re: Color background expression when filtering by tables.

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