Expression help in text Object

[#1]

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

[Aurelien_Martinez]

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'

  )

)

[#3]

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

[Gysbert_Wassenaar]

=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'

  ))

[#5]

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

[#6]

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

[Gysbert_Wassenaar · Accepted solution]

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'

  )

)

[#8]

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

[Gysbert_Wassenaar]

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

[#10]

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