topic Re: How can we do conditional coloring based on the % column? in QlikView

How can we do conditional coloring based on the % column?

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

I have 2 columns as shown below (also have attached a sample excel)

I would like to do a conditional coloring but ... here comes the challenge

The first column is the TIMESTAMP which is a record poll of every 10 minutes.

I want to to show the BW Level % as

YELLOW if it is above 75%

GREEN if below 75%

RED if above 75% but sustaining for more than 10 minutes continuously.

We can create a formula to say whether it is above 75% or not but I dont know how to analyze if BW level was sustaining continuously for more than 10 minutes.

Really dont know how to do this and need you help please.

TimeStamp	BW % Level
4/20/16 12:00 AM	90%
4/20/16 12:10 AM	10%
4/20/16 12:20 AM	10%
4/20/16 12:30 AM	10%
4/20/16 12:40 AM	10%
4/20/16 12:50 AM	10%
4/20/16 1:00 AM	10%
4/20/16 1:10 AM	80%
4/20/16 1:20 AM	85%
4/20/16 1:30 AM	92%
4/20/16 1:40 AM	90%
4/20/16 1:50 AM	10%
4/20/16 2:00 AM	10%
4/20/16 2:10 AM	10%
4/20/16 2:20 AM	10%
4/20/16 2:30 AM	10%
4/20/16 2:40 AM	10%
4/20/16 2:50 AM	90%
4/20/16 3:00 AM	10%
4/21/16 12:00 AM	10%
4/21/16 12:10 AM	10%
4/21/16 12:20 AM	10%
4/21/16 12:30 AM	10%
4/21/16 12:40 AM	10%
4/21/16 12:50 AM	90%
4/21/16 1:00 AM	10%
4/21/16 1:10 AM	10%
4/21/16 1:20 AM	10%
4/21/16 1:30 AM	10%
4/21/16 1:40 AM	10%
4/21/16 1:50 AM	10%
4/21/16 2:00 AM	90%
4/21/16 2:10 AM	10%
4/21/16 2:20 AM	10%
4/21/16 2:30 AM	10%
4/21/16 2:40 AM	10%
4/21/16 2:50 AM	10%
4/21/16 3:00 AM	10%

Re: How can we do conditional coloring based on the % column?

sunny_talwar — Wed, 20 Apr 2016 09:35:37 GMT

Is this how the raw data comes for you? in percentage form?

Re: How can we do conditional coloring based on the % column?

Chanty4u — Wed, 20 Apr 2016 09:36:02 GMT

try this

BW Level % as

YELLOW if it is above 75%

GREEN if below 75%

RED if above 75%

=if([ BW Level %]>75%, yellow(), if([ BW Level %]<=75%, Green() ,Red())

Re: How can we do conditional coloring based on the % column?

kkkumar82 — Wed, 20 Apr 2016 09:38:17 GMT

I think the red color is tricky here chanty, it may not be always correct if all the two conditions are not met

Re: How can we do conditional coloring based on the % column?

kkkumar82 — Wed, 20 Apr 2016 09:41:45 GMT

Hi Vijay ,

Can you high the rows which must be in red in your above data

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:01:18 GMT

Yes this is how I get the raw data..

Trick we could say if it is above or below 75% but We have to see if it is above 75% and continuously sustaining for more than 10 minutes then I have to say it as RED

Re: How can we do conditional coloring based on the % column?

kkkumar82 — Wed, 20 Apr 2016 12:04:26 GMT

In your data you posted do you have any of the rows which have %>75 and last long for more than 10 minutes

Re: How can we do conditional coloring based on the % column?

ThornOfCrowns — Wed, 20 Apr 2016 12:10:26 GMT

You will probably need to look at the last row of data to see if that breaches the %age then use that as an AND in your conditional formatting expression.

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:37:47 GMT

See the entries below pls

4/20/16 1:10 AM	80%
4/20/16 1:20 AM	85%
4/20/16 1:30 AM	92%
4/20/16 1:40 AM	90%

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:38:02 GMT

4/20/16 1:10 AM	80%
4/20/16 1:20 AM	85%
4/20/16 1:30 AM	92%
4/20/16 1:40 AM	90%

Re: How can we do conditional coloring based on the % column?

ThornOfCrowns — Wed, 20 Apr 2016 12:38:40 GMT

Try this in your background colour expression:

if([BW % Level]>0.75 and above([BW % Level],1)>0.75 ,Red(),

if([BW % Level]>0.75,Yellow()

,Green()
))

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:45:13 GMT

@ James I am looking a final sumarry result as 20-APR-2016 with a RED color since we have >75% continously occured on this particular day

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:47:04 GMT

apologies If I am not clear for 20th APR we have entries and since had >75% entried continousuly without breakup the 20th Apr final summary should be red. If not continous entries for >75% then final summary should be YELLOW if not entries for 75% at all then green

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 12:49:23 GMT

Re: How can we do conditional coloring based on the % column?

ThornOfCrowns — Wed, 20 Apr 2016 13:35:09 GMT

That's a totally different question to the one you posted originally.

Re: How can we do conditional coloring based on the % column?

Anonymous — Wed, 20 Apr 2016 14:00:45 GMT

Yes but that was my actual requirement. Need your help on this

On Wed, Apr 20, 2016 at 7:05 PM, James Summerson <qcwebmaster@qlikview.com>