Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.....)
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 |
When I pick the second Table, by Worker, in this case PATRICK. I get the following in the first table.
Date | Day | Extra Hours |
---|---|---|
20/02/2016 | Saturday | 12 |
20/03/2016 | Saturday | 8 |
17/01/2016 | Saturday | 4 |
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.
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() ))))
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() ))))
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.
I doubt that Replace(Nombre, ',', ' ') returns the name of a field. Try just Nombre instead
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.
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.
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.
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.
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!
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() ))))