topic Traffic lights in QlikView

Traffic lights

Thu, 08 Dec 2011 14:07:57 GMT

Hi,

I have a bar chart with on the x-axis employeeID and on the y-axis is the sales

Now I want to make a traffic light which is green if EACH employee meets the target of 60 000 000 sales and red if they don't meet the target.

But how do I do this?

Can I use my bar chart for it or should I make a new gauge chart with exact the same data?

And we're can i say when it should be green or red?

Thanks in advance!

Traffic lights

Thu, 08 Dec 2011 14:27:43 GMT

Hello,

You can play around with the legend on the bar chart to do it or its always suggested if you use a Guage chart with LED's.

Anyways you need to write something like if(sum(Sales) = '60000000', Green(), Red()). This should be your expression and the Dimension should be your Employee.

Hope this helps.

Thanks

Traffic lights

matt_crowther — Thu, 08 Dec 2011 14:29:16 GMT

If you mean; green if every induvidual employee reaches the 60,000,000 target and red if even 1 falls short then look at a single LED gauge chart and an expression using an agg() function across the EmployeeID dimension.

If you want a traffic light for each seperate employee you can use a straight table chart with EmployeeID as the dimension and a micro-chart as the expression with a simple if() controling the LED component.

You could also add an indicator to the Axis of the bar chart ('Text on Axis') you may be able to get a red / green LED to display by using the built in QV icons.

Alternatively I'd look to put a reference line in your Bar Chart at a y-value of 60,000,000 you can also colour the bar itself red or green accordingly with a simple background color if() function. This would be my preferred option for ease of use.

Hope that helps,

Matt - Visual Analytics Ltd

Traffic lights

Thu, 08 Dec 2011 14:52:57 GMT

If I get you right I think you want this.... not sure it will look good but try it and see what you think...

in the expression tab of your object properties. click the expand button next to your expression for sales.

then on the background color tool..

then put the following if statement into the definition box on the right..

if(Sales>='60000000',Green(),Red())

I hope this helps..

Traffic lights

Thu, 08 Dec 2011 14:57:59 GMT

as a thought it may be worth also applying a ref line in your graph to help the user understand the reason for the colour coding.

just a thought.

Traffic lights

Thu, 08 Dec 2011 21:51:16 GMT

On my y-axis is sum(Subtotal), which I call sales.

I've made a new text object with code:

=If(sum(Subtotal)>'60000000', 'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_r.png')

but it is always green, i think it's because it doesn't look at the seperate employeeID's, how can i let him check every sum(Subtotal) of every employeeID? And only if every employeeID meets the target of 60000000 it should be green.

Thanks in advance!

Traffic lights

Fri, 09 Dec 2011 08:56:51 GMT

Could you upload an example of your graph so that I can look at this please. ??

I am finding it difficult to picture it in my head. .

Thanks

Re: Traffic lights

Fri, 09 Dec 2011 09:26:12 GMT

Here is a small example

Re: Traffic lights

Fri, 09 Dec 2011 10:25:16 GMT

Hi, it would be good if you use a table instit of a chart in that you can show an additional column and show that traffic lights attached is an example for you reference

Re: Traffic lights

Fri, 09 Dec 2011 10:28:45 GMT

I have applied two different ways of visualizing this info..

Please see the attached example.

I hope one of these covers your needs

Note: the traffic light way can get messy if you have a huge list of Employees as it could require a lot of future maintenance.

Re: Traffic lights

Fri, 09 Dec 2011 10:31:35 GMT

I can't open it because i'm using personal edition.

It says if i want to repair it but than i won't be able anymore to open things with my one userid.

But i'll try to see if i can try it on another pc

Thanks a lot!

Re: Traffic lights

Fri, 09 Dec 2011 10:38:40 GMT

in the traffic light use this

=If(sum({$<EmployeeID={1}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={2}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={3}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={4}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={5}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={6}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={7}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={8}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={9}>}Subtotal)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

'qmem://<bundled>/BuiltIn/led_g.png')))))))))

the example of the chart was this..

My personal view is that the chart does what you need in its own right..

But should you need to apply the traffic lights the formula should set you on your way.

Re: Traffic lights

Fri, 09 Dec 2011 10:49:42 GMT

Thanks a lot, i understand it now!

One last question:

I've changed sum(subtotal) in to sales in my database, how should i write the if now?

Like this?

= If({$<EmployeeID={1}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

Re: Traffic lights

Fri, 09 Dec 2011 10:53:21 GMT

That is right just replace the Subtotal with sales.... like this

=If(sum({$<EmployeeID={1}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={2}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={3}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={4}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={5}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={6}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={7}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={8}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If(sum({$<EmployeeID={9}>}Sales)<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

'qmem://<bundled>/BuiltIn/led_g.png')))))))))

Re: Traffic lights

Fri, 09 Dec 2011 10:55:10 GMT

Should sum stay?

Cause sum(Subtotal) = sales

Re: Traffic lights

Fri, 09 Dec 2011 10:59:38 GMT

yes sorry that is right...

so if you now have the sum figure as a single figure in your database this is fine..

sorry I should have read your post better..

Traffic lights

Fri, 09 Dec 2011 11:04:48 GMT

It doesn't work, i have:

=If({$<employeenumber={1}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={2}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={3}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={4}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={5}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={6}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={7}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={8}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

If({$<employeenumber={9}>}sales<100000, 'qmem://<bundled>/BuiltIn/led_r.png',

'qmem://<bundled>/BuiltIn/led_g.png')))))))))

I changed:

employeeID --> employeenumber

sum(subtotal) --> sales

i'm getting crazy, i guess

Traffic lights

Fri, 09 Dec 2011 11:07:57 GMT

Try putting the sum back in before the set.

This should work.

good luck

Traffic lights

Fri, 09 Dec 2011 11:37:09 GMT

That worked! Thanks a lot!

Would it also be possible to say at least 80 % of the employees has to meet the target?

If yes, how should I do that??