topic Re: Problem with Num function in QlikView

Problem with Num function

Mon, 06 Jan 2014 10:51:21 GMT

Hi Friends,

Facing an issue with the number formatting, when I used the Num function on a negative value as Num(InVoiceBudget_Amt_TY,'#,##0.0#;-#,##0.0#','.',',') it gives a positive value as 11088128.1 which is actually -6421321.059525 at back end

If anybody has faced this issue earlier, could you share the details of possible solution?

Thanks,

Raj

Re: Problem with Num function

rustyfishbones — Mon, 06 Jan 2014 10:55:25 GMT

Hi Rajesh

try this removing the ,'.',','

Num(InVoiceBudget_Amt_TY,'#,##0.0#;-#,##0.0#')

Re: Problem with Num function

Mon, 06 Jan 2014 11:00:35 GMT

Hi,

Try this

= num(-6421321.059525 ,'#,##0.0')

It will return -6,421,321.1, No matter the number is positive or negative... Use InVoiceBudget_Amt_TY instead of -6421321.059525

Thanks,

Selvakuamr

Re: Problem with Num function

Tue, 07 Jan 2014 02:26:28 GMT

Hi Alan,

Thanks for your time and reply, I see removing the decimal and thousand separator is not an option for me, because my local environment has '.' as decimal separator and ',' as thousand separator, where as the QA and Prod environments uses them other way around, as the servers located in Europe.

I have tried over coming this by setting the system variables, but unfortunately that doesn't seems to serve the purpose, or may be I am missing some thing while setting the system variables, I used the below statements for setting the system variables

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='¥#,##0.00;¥-#,##0.00';

SET NumberFormat='#,##0.0#;-#,##0.0#';

So my whole problem is about using the consistent decimal and thousand separators throughout the environments, I thought I could force it by using Num function, since setting the system variables didn't serve the purpose.

Does this strike any possible solution for you?

my sincere thanks for your time...

Raj

Re: Problem with Num function

Anonymous — Tue, 07 Jan 2014 04:12:04 GMT

I checked your formula only and I got the correct result,

=num(-6421321.059525 ,'#,##0.0#;-#,##0.0####','.',',')

Can you post the sample app showing error

Re: Problem with Num function

er_mohit — Tue, 07 Jan 2014 04:24:30 GMT

if you want as same as your field then use text function i.e

text()

or you can use num function also like this

Num(if(len(InVoiceBudget_Amt_TY)>0,InVoiceBudget_Amt_TY),'#,##0.0#;-#,##0.0#')

hope it helps

Re: Problem with Num function

Tue, 04 Feb 2014 04:06:54 GMT

Thanks for your valuable suggestions friends,

I found the problem is at data source - Ms Access selection query level itself, from the Access SQL the data returned is already formatted before it reaches the QV Load statement according to the regional settings, resolved this by resetting the Region & Language settings.

Best Regards,

Rajesh