topic Re: Num () question in QlikView

Num () question

devans_1 — Wed, 07 May 2014 13:06:28 GMT

I have some data that has been created using SUM (). Some of it is integer, some of it is not. I would like integers displayed without decimal places. But I would like the data that contains decimal places to stop at 2 dp. e.g. Data of

3.45678

would display as

3.46

If I just display the data, I get

3.45678

If I use the NUM function (ie NUM (<field>, '#,##'), I get

1.00

2.00

3.46

Does anyone know what I am doing wrong with NUM ? My understanding was that the use of the hash meant the decimal places were flexible and would not appear if they weren't there but they are appearing.

Re: Num () question

javier_florian — Wed, 07 May 2014 13:16:20 GMT

Hi David,

Your function is fine, format is applied to all data.

-JFlorian

Re: Num () question

Wed, 07 May 2014 13:16:30 GMT

Hi,

You can round numbers with 2 decimals in your object : Round(NumericField,0.01) then display numbers naturally.

Re: Num () question

ashfaq_haseeb — Wed, 07 May 2014 13:17:22 GMT

Try below

num(Num,'#######,###.##')

Regards

ASHFAQ

Re: Num () question

Anonymous — Wed, 07 May 2014 13:17:29 GMT

num function will format all the numeric values in that field/column to the format you specify.

Re: Num () question

Wed, 07 May 2014 13:21:30 GMT

Did you set any Number format under Number tab of Object properties?

Re: Num () question

rajat2392 — Wed, 07 May 2014 13:24:55 GMT

Hi David,

Try this expression

if(Subfield(<field>,'.',2)>0,Num(<field>,'#.00'),<field>)

Regards

Rajat

Re: Num () question

devans_1 — Wed, 07 May 2014 13:30:37 GMT

Thanks for your replies. I realise that the format is applied to all values in the column - it's just that I am trying to find the correct format that will remove trailing decimal places.

num (NUM, '#######,###.##')

unfortunately produces the same results as my initial problem. I have considered ROUND and may have to go down this route. It's not perfect as ROUND also adds decimal places when they are not required but if used earlier in the script and then later summed, unecessary trailing dps are removed.

Re: Num () question

devans_1 — Wed, 07 May 2014 13:35:15 GMT

I have tried changing the number format but this just places trailing zeroes. Rajat's answer would work but i smessy because I have to apply it to 12 different complicated expressions. I think ROUND is the best answer to go with.

Thanks for all your help.

Re: Num () question

its_anandrjs — Wed, 07 May 2014 13:43:42 GMT

Try to load your table like below script if you want a digit number 1.345 to be 1.34 or 2.145 to be 2.14 or 3.45678 to be 3.45 then try to load like below table

LOAD *,Num(SubField(Number,'.',1)&'.'&Left( SubField(Number,'.',-1),2)) as NewNumber;

LOAD * Inline

[

Number

1.345

2.145

3.45678

];

OutPut will be

==========

1.34

2.14

3.45

Re: Num () question

Wed, 07 May 2014 13:46:31 GMT

NUM (<field>, '#,##') , This format works, but don't set any number setting (dont override document settings)on the chart for this field.

Re: Num () question

ToniKautto — Wed, 07 May 2014 15:08:08 GMT

My recommendation is to avoid using text function like Subfield(), since text operations are significantly slower in performance than numerical operations.

With Frac() it is possible to remove the integer part from a value, and if the decimal part is not 0 then the value is a decimal value and should be rounded.

Notice that Round() will adapt both the visible value and the underlying value, while Num() will keep the underlying decimal value and just adapt the visual value. So the selected function to adapt the value depends on what precision you want to have on calculations.

LOAD

If(Frac(F1)<>0,Round(F1,'0.01'),F1) AS F1

Inline [

3.45678

];

Re: Num () question

PrashantSangle — Wed, 07 May 2014 15:22:26 GMT

Hi,

Try this,

=if(len(fmod(field,10))>1,num(field,'##.##'),num(field,''))

Regards,

Re: Num () question

its_anandrjs — Wed, 07 May 2014 15:28:50 GMT

Otherwise you can simply try with

Num(YourFieldHere,'##.##') as NewNum;