topic Re: Problem with Conditional Colors on Text in QlikView

Problem with Conditional Colors on Text

rponichtera — Wed, 25 Nov 2020 16:16:04 GMT

I am having a problem with conditional formatting for text colors on screen. I have reused code that is working on other screens but with one big difference. I am trying to do a calculation on the expression and not a straight look at a single field. The code looks like the following:

=if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '01:30:00', blue(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName)))>='01:30:01'

AND (Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting, Branch, ServiceName))) <= '02:00:00', yellow(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName)))>='02:00:01'

AND (Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '02:30:00', red(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) > '02:30:00', black(), rgb(0, 87, 60)))))

I get the correct time for when I use the time calculation in the Field expression but it seems to fail in the conditional text color formatting.

Re: Problem with Conditional Colors on Text

JaMajka1 — Wed, 03 Jan 2018 00:19:14 GMT

Hi Robert,

try to use frac() function instead of defining date format - here is simpler version of your function:

if(frac(Now()) <= frac(Date#('01:30:00','hh:mm:ss')), blue())

Hope it helps,

Maria

Re: Problem with Conditional Colors on Text

MayilVahanan — Wed, 03 Jan 2018 06:01:25 GMT

Note that, you are subtracting 2 times and comparing with string, its make the expression failed.

=if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '01:30:00' <- String

Instead of that, you need to convert the string to time by using time# function. and also, recommend to use time function in the Avg also.

All 3 must have in same format.

Re: Problem with Conditional Colors on Text

rponichtera — Wed, 03 Jan 2018 15:37:56 GMT

I have taken both suggestions and tried them . For simplicity I changed the conditional formatting to be on one time frame only. Unfortunately I am still not getting any positive results. The code now looks like:

if((Time#(Now(),'hh:mm:ss') - Time#(AVG(AGGR(AVG({$<ServiceName = 'Renew'>} LongestWaiting), Branch, ServiceName)))) <= Time#('01:30:00','hh:mm:ss'), blue(), red())

Re: Problem with Conditional Colors on Text

MayilVahanan — Thu, 04 Jan 2018 05:29:59 GMT

Can you provide some sample data.

Re: Problem with Conditional Colors on Text

rponichtera — Wed, 10 Jan 2018 20:30:43 GMT

I have attached a qvf of the app I am working on.