Solved: Re: Expression Not working - Qlik Community

NavinReddy · ‎2016-06-16

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

jonathandienst · ‎2016-06-17

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)
  )
)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

er_mohit · ‎2016-06-16

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))))

NavinReddy · ‎2016-06-16

Hi Mohit,

Thanks for reply, Still its giving error

NavinReddy · ‎2016-06-16

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))))

Anonymous · ‎2016-06-16

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.

sasikanth · ‎2016-06-16

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))))

NavinReddy · ‎2016-06-16

Hi Sasi,

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

i have attached the sample app please check

NavinReddy · ‎2016-06-16

Hi Alex,

i have tried with your expression still its giving problem

can you please look into it any suggessions

NavinReddy · ‎2016-06-17

Good Morning to all

Any suggessions

Digvijay_Singh · ‎2016-06-17

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.