topic Conditional Formatting in Expression in QlikView

Conditional Formatting in Expression

mjt_childrens — Fri, 30 Jul 2010 21:31:35 GMT

Hi All....

I have a scorecard straight table where MetricId is my dimension. I want to format an expression as integer, percent or currency depending on the metricid. Here is a simplified test case I've been working with:

= if(MetricId=2, num(count(CRMRecType),'###,###,##0'),

If(MetricId=5,num(count(CRMRecType)/100,'#,##0.00%'),

If(MetricId=22,num(sum(OpportunityAmount),'$##,###,###'))))

Number format for this expression is set to "Expression Default".

My calculations are working correctly but the formatting is being ignored. Any one of these format statements by itself works correctly but when I put them together the formatting is ignored. What am I missing?

Thanks for any insight into this issue....

Martha

Conditional Formatting in Expression

Fri, 30 Jul 2010 22:14:55 GMT

How about using the money() function rather than num()?

Conditional Formatting in Expression

mjt_childrens — Fri, 30 Jul 2010 22:18:42 GMT

Doesn't change the outcome -- still no formatting.

Conditional Formatting in Expression

johnw — Fri, 30 Jul 2010 23:24:44 GMT

Looks right to me. Is the format being overridden on the Number tab? I'm guessing not, since you say each one works individually.

Conditional Formatting in Expression

mjt_childrens — Sat, 31 Jul 2010 20:05:47 GMT

I selected "expression default" on the number tab.

Conditional Formatting in Expression

jonathandienst — Mon, 02 Aug 2010 11:26:04 GMT

Martha

I suspect the if statement evaluates the expression and returns the evaluated value as a number, so the formatting is applied before and not after the if expression.

As a work around, you could try wrapping the num() statements with a text() statement. Then it is a text expression being evaluated.

I haven't tested this, so I can't be sure it will work. Give it a try and let us know.

Jonathan

Conditional Formatting in Expression

mjt_childrens — Mon, 02 Aug 2010 19:43:02 GMT

Jonathan,

Wow, you figured it out. I added the text statement and it works! Thank you so much.

Martha

(MetricId=2,text(num(count(CRMRecType),'###,###,##0')),

Conditional Formatting in Expression

suniljain — Thu, 02 Feb 2012 08:18:32 GMT

In case of line chart . this logic is not working.

Re: Conditional Formatting in Expression

Fri, 20 Jul 2012 19:41:48 GMT

Hi Sunil, did you ever find a fix for this in the case of a line chart? I am having the same problem. I tried wrapping text() function around the formatting but its not doing anything to change my line chart formatting.

Thanks, Sarah

Re: Conditional Formatting in Expression

pennetzdorfer — Wed, 28 Nov 2012 16:31:39 GMT

Hey,

I've got a bar chart showing the annual sales deviation per region. Through a button the user can switch between relative (%) and absolute (€) deviation. Through an if statement and the num() function i can format the "Values on Data Points" properly.

Unfortunately this doesn't work for the y-axis scale labels. They aren't formatted at all. Does anyone have an idea why?

Regards,

Florian

Re: Conditional Formatting in Expression

Anonymous — Fri, 11 Nov 2016 11:24:54 GMT

please try this on i think it solved problem of conditional number formatting

num((if(vSelectionButton=1,Sum(expression1)

,if(vSelectionButton=2,Sum(expression1)

))),if(vSelectionButton=1,'$ #,##','#,##0.00'))

Reagrds,

Dixit Panchal