topic Re: Expression help in text Object in QlikView

Expression help in text Object

Wed, 23 Oct 2013 10:15:27 GMT

Hi,

I have the below expression which i need to show the RAG status in the Text object. I am not sure how to write the below exp for the RAG status.

=ceil(count(if ([status]=1,Employee))/count(if([status]='1','0',Employee))

(i.e, count of EMPLOYEES when STATUS=1 divided by Count of EMPLOYEES when STATUS=1 and 0)

RAG should be:

0-75 Red
75-85 Amber
85-100 Green

I want to use images 'qmem://<bundled>/BuiltIn/led_r.png', 'qmem://<bundled>/BuiltIn/led_y.png', 'qmem://<bundled>/BuiltIn/led_g.png' for Red, Amber and Green.

Can anyone help me out please?

Thanks

Re: Expression help in text Object

Aurelien_Martinez — Wed, 23 Oct 2013 10:20:56 GMT

Hi,

=If(Ceil(count({<status=1>} Employee) / count({<status=0>} Employee) < 75,

'qmem://<bundled>/BuiltIn/led_r.png',

If(Ceil(count({<status=1>} Employee) / count({<status=0>} Employee) < 85,

'qmem://<bundled>/BuiltIn/led_y.png',

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

)

Re: Expression help in text Object

Wed, 23 Oct 2013 10:26:53 GMT

Hi Aurien,

Thanks for your reply. I need to count both 1 and 0 in the denominator but in your expression its just counting Status=0. Is your expression correct ?

Thanks

Re: Expression help in text Object

Gysbert_Wassenaar — Wed, 23 Oct 2013 10:31:12 GMT

=If(Ceil(count({<status={1}>} Employee) / count({<status={0,1}>} Employee)) < 75,

'qmem://<bundled>/BuiltIn/led_r.png',

If(Ceil(count({<status={1}>} Employee) / count({<status={0,1}>} Employee)) < 85,

'qmem://<bundled>/BuiltIn/led_y.png',

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

))

Re: Expression help in text Object

Wed, 23 Oct 2013 10:38:08 GMT

info(if(count({<Status={1}>}Employee)/count({<Status={0,1}>}Employee)<=75,

'qmem://<bundled>/BuiltIn/led_r.png',

if(count({<Status={1}>}Employee)/count({<Status={0,1}>}Employee)<=85,

'qmem://<bundled>/BuiltIn/led_y.png',

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

try this

Re: Expression help in text Object

Wed, 23 Oct 2013 11:12:25 GMT

Hi wasseaar,

I tried your expression but getting 'error in expression'. Might be somewhere brackets are missing.

here is my expression:

=If(ceil(count({<[status]={1}>} Employee) / count({<[status]={0,1}>} Employee) < 75,'qmem://<bundled>/BuiltIn/led_r.png'
, If(ceil(count({<[status]={1}>} Employee) / count({<[status]={0,1}>} Employee) <85, 'qmem://<bundled>/BuiltIn/led_y.png','qmem://<bundled>/BuiltIn/led_g.png'))

I try to add 2 closed brackets at the end of the expression but still getting error in expression. could you check it for me please?

Thanks

Re: Expression help in text Object

Gysbert_Wassenaar — Wed, 23 Oct 2013 11:22:43 GMT

You're correct. Try this one:

=If(Ceil(count({<status={1}>} Employee) / count({<status={0,1}>} Employee)) < 75,

'qmem://<bundled>/BuiltIn/led_r.png',

If(Ceil(count({<status={1}>} Employee) / count({<status={0,1}>} Employee)) < 85,

'qmem://<bundled>/BuiltIn/led_y.png',

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

)

Re: Expression help in text Object

Wed, 23 Oct 2013 13:07:19 GMT

thanks Wassenaar. Working fine now. I need one more clarification. I created an object with below expression.

=ceil(count(if ([status]=1,Employee))/ count(if([status]='1','0',Employee))

I am not sure whether my expression is correct or not. My requirement is to Count all the employees who are in status 1, divided by, count of all employees who re in status 1 and 0. Is there any otherway to write the above expression ?

Thanks

Re: Expression help in text Object

Gysbert_Wassenaar — Wed, 23 Oct 2013 13:11:03 GMT

It's not correct. This would be: =ceil(count(if ([status]=1,Employee))/ count(if([status]=1 or [status]= 0,Employee))

You could also write it like this:

=ceil(count({<[status]={1}>}Employee)/count({<[status]={1,0}>}Employee))

On large datasets this would perform better than the expression with the if statements

Re: Expression help in text Object

Wed, 23 Oct 2013 13:20:49 GMT

Excellent, I tried the 2nd one (the one without the If statement) and is working fine. Many thanks!