topic Re: Expression Not working in QlikView

Expression Not working

NavinReddy — Thu, 16 Jun 2016 11:26:37 GMT

Dear Experts,

Not working below expression.

When ELE_VALUE_TYPE_CD = 'PCT' i have to evaluate the value with 100 and i want to include %

its not working for '0' values its displaying '0', its should display "0%"

i have tried with below two expression its not working

please any suggessions

=if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',Num((GREEN_RAG)*100,'###.00000'&'%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num((HIGH_GREEN_RAG),'##.00'&'%')&' '&' '&Num((GREEN_RAG),'##.00'&'%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)&' '&Num(GREEN_RAG))))

=if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',Num((GREEN_RAG),'##.00%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT',Num((HIGH_GREEN_RAG),'##.00%')& '' & Num((GREEN_RAG),'##.00%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)& ' ' & Num(GREEN_RAG))))

Best Regards,

Niranjan

Re: Expression Not working

er_mohit — Thu, 16 Jun 2016 11:52:22 GMT

Try this

=if(isnull(ELE_VALUE_TYPE_CD),

if(ELE_VALUE_TYPE_CD = 'PCT',Num((GREEN_RAG)*100,'###.00000'&'%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num((HIGH_GREEN_RAG),'##.00'&'%')&' '&' '&Num((GREEN_RAG),'##.00'&'%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)&' '&Num(GREEN_RAG))))

Re: Expression Not working

NavinReddy — Thu, 16 Jun 2016 12:04:52 GMT

Hi Mohit,

Thanks for reply, Still its giving error

Re: Expression Not working

NavinReddy — Thu, 16 Jun 2016 12:07:53 GMT

Hi Mohit,

i have used below expression getting one extra '%', suggest me how can i remove extra '%'

=if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',num((GREEN_RAG)*100,'##.00000'&'')&'%',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT',num((HIGH_GREEN_RAG)*100,'##.00000'&'')&'%'& '' & num((GREEN_RAG)*100,'##.00000'&'')&'%',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)& ' ' & Num(GREEN_RAG))))

Re: Expression Not working

Anonymous — Thu, 16 Jun 2016 12:17:29 GMT

Try this:

=if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',num((GREEN_RAG)*100,'#,##0%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT',num((HIGH_GREEN_RAG)*100,'#,##0%')& num((GREEN_RAG)*100,'#,##0%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)& ' ' & Num(GREEN_RAG))))

Also untick 'supress zero-values' from the presentation tab.

Re: Expression Not working

sasikanth — Thu, 16 Jun 2016 12:41:58 GMT

try this

=if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',num((GREEN_RAG)*100,'#,##0.0000%')

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT',num((HIGH_GREEN_RAG)*100,'#,##0.0000%')& '' & num((GREEN_RAG)*100,'#,##0.0000%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)& ' ' & Num(GREEN_RAG))))

Re: Expression Not working

NavinReddy — Thu, 16 Jun 2016 20:31:17 GMT

Hi Sasi,

its not working some of values i dont have the% symbol

i have attached the sample app please check

Re: Expression Not working

NavinReddy — Thu, 16 Jun 2016 20:32:13 GMT

Hi Alex,

i have tried with your expression still its giving problem

can you please look into it any suggessions

Re: Expression Not working

NavinReddy — Fri, 17 Jun 2016 04:43:26 GMT

Good Morning to all

Any suggessions

Re: Expression Not working

Digvijay_Singh — Fri, 17 Jun 2016 05:00:22 GMT

Can you explain the logic behind below -

if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',num((GREEN_RAG)*100,'#,##0.0000%')

As I see the same variable first checked if it is '-', not able to understand why, if it is true, then same variable is checked if it is 'PCT'. How one variable can have two values at the same time. I know I am missing something here.

Re: Expression Not working

jonathandienst — Fri, 17 Jun 2016 05:03:19 GMT

I am not sure exactly what you are trying to do, because your expression is logically inconsistent. You provide for 3 possible values (is the - supposed to be checking for null?), but the true condition for the '-' will always return null, and only the second part can ever produce a value.

I assume that you want to check if HIGH_GREEN_RAG is empty/null, then your expression would look like:

=If(Len(HIGH_GREEN_RAG) = 0,
  If(ELE_VALUE_TYPE_CD = 'PCT',
    Num((GREEN_RAG),'##.00%'),
    If(ELE_VALUE_TYPE_CD = 'QTY', Num(GREEN_RAG))
  ),

  If(ELE_VALUE_TYPE_CD = 'PCT',
    Num(HIGH_GREEN_RAG, '##.00%') & ' ' & Num(GREEN_RAG, '##.00%'),
    If(ELE_VALUE_TYPE_CD = 'QTY',
      Num(HIGH_GREEN_RAG) & ' ' & Num(GREEN_RAG)
    )
  )
)

Make sure that you leave the format in the Number tab as 'expression default'.

Re: Expression Not working

jonathandienst — Fri, 17 Jun 2016 05:05:59 GMT

If ELE_VALUE_TYPE_CD can only have two possible values (PCT or QTY), then you can simplify further:

=If(Len(HIGH_GREEN_RAG) = 0,
  If(ELE_VALUE_TYPE_CD = 'PCT',
    Num((GREEN_RAG),'##.00%'),
    Num(GREEN_RAG)),

  If(ELE_VALUE_TYPE_CD = 'PCT',
    Num(HIGH_GREEN_RAG, '##.00%') & ' ' & Num(GREEN_RAG, '##.00%'),
    Num(HIGH_GREEN_RAG) & ' ' & Num(GREEN_RAG)
  )
)

Re: Expression Not working

NavinReddy — Fri, 17 Jun 2016 10:11:09 GMT

HI Jonathan,

Thanks for your reply, Yes your correct if HIGH_GREEN_RAG is empty/null,one expression or else including HIGH_GREEN_RAG one expression

i think your both expression right but here is the problem

when GREEN_RAG =0 its displaying "0" but i have to display "0%"

on 0 location i have to display 0%, i have tried but im not able to archive

any suggestions

Regards,

Niranjan

Re: Expression Not working

NavinReddy — Fri, 17 Jun 2016 10:13:12 GMT

Hi Digvijay,

Thanks for your reply

if ELE_VALUE_TYPE_CD = emply/null one expression else part including HIGH_RED_RAG

Thanks,

Niranjan

Re: Expression Not working

Anonymous — Fri, 17 Jun 2016 11:15:14 GMT

Why don't you try with the expression Jonathan gave you as a chart expression instead of a calculated dimension?

It should work.

Best regards,

Alex

Re: Expression Not working

NavinReddy — Fri, 17 Jun 2016 16:29:33 GMT

Thanks Every one

Re: Expression Not working

jonathandienst — Mon, 20 Jun 2016 07:38:17 GMT

>>when GREEN_RAG =0 its displaying "0" but i have to display "0%"

Are you sure that ELE_VALUE_TYPE_CD = PCT for these values?