topic Re: Color Code individual Bars in QlikView

Color Code individual Bars

Tue, 29 Jan 2013 13:35:37 GMT

Hi Guys,

I am making a bar chart ( below)

I need to colour code these bars depending on the values they show.

However, I need to do this on a Bar by Bar basis.

(I asume in the Background Color section of the expressions tab)

<7 days

green : >90%

yellow: Between 85% and 90%

red: <85%

between 7 and 14 days

Green: <9%

Yellow: Between 9% and 15%

Red: >15%

between 14 and 21 days

Green: <3%

Yellow: Between 3% and 5%

Red: >5

>21 days

Green : 0

Red: >0

The expression which is currently being used to generate this chart is:

=COUNT(Call_Priority)/COUNT(TOTAL ID)

The dimention used is the field Call_Priority

Can this even be done in Qlikview?

Any help appreciated.

Re: Color Code individual Bars

Gabriel — Tue, 29 Jan 2013 13:52:22 GMT

Hi Levi,

What I suggest you do is go to Chart Properties > Expression > click the + sign beside your dimension.

Select Background Color > then pop this expression into the expression field

if(COUNT(Call_Priority)/COUNT(TOTAL ID)>90%, Green(), if(COUNT(Call_Priority)/COUNT(TOTAL ID)>85% &<=90%,Yellow(), if(COUNT(Call_Priority)/COUNT(TOTAL ID)<=85%,Red()))))

Start to experiment bit by bit

Click ok

Try this and let me know if it works.

Regards,

Gabriel

Re: Color Code individual Bars

Tue, 29 Jan 2013 13:53:20 GMT

Yes, it can be done. Maybe you have not written the condition correctly. Could you attach a sample document?

Re: Color Code individual Bars

Tue, 29 Jan 2013 13:59:33 GMT

Thanks Gabriel,

This will colour all of the bars the same colour basing it on the same threshold.

I need a solution which will colour each bar individually based on it's own threshold.

Bar 1 (<=7) should turn green when the value is greater than 90%

but Bar 2(>7 <=14) should turn green when the value is less than 9 %

hope this clarifies things

Re: Color Code individual Bars

Tue, 29 Jan 2013 14:18:33 GMT

Thanks for your help,

I have attached a datafile here and removed all sensitive data.

The cart is found on the Resolution Status sheet.

Thank You

Re: Color Code individual Bars

Tue, 29 Jan 2013 14:18:58 GMT

Thanks Juan,

I attached a sample below

Re: Color Code individual Bars

Tue, 29 Jan 2013 15:10:10 GMT

Using IF() conditions in the background color expression, you have this result:

Attached you have also the document with the expresion:

(Call_Priority = '<=7',

If(Metric >= 0.9, Green(),

If(Metric >= 0.85, Yellow(), Red())

),

If(Call_Priority = '>7 <=14',

If(Metric < 0.09, Green(),

If(Metric < 0.15, Yellow(), Red())

),

If(Call_Priority = '>14 <=21',

If(Metric < 0.03, Green(),

If(Metric < 0.05, Yellow(), Red()

)

),

If(Call_Priority = '>21',

If(Metric = 0, Green(),

If(Metric > 0, Red())

)

)

)

)

)

Re: Color Code individual Bars

Tue, 29 Jan 2013 15:34:47 GMT

A round of applause for Juan!

This is a perfect solution, does exactly what I need it to and I have also learnt how to do this in the future!

Thank you so much!