topic Re: Convert negative number value in QlikView

Convert negative number value

Wed, 22 Feb 2017 10:14:07 GMT

Hi,

How do we do for converting negative number? i have some value like -1.78967*******e-16, i want this to be displayed either as 0 or some number with just 2 decimals (like -1.78f). I tried using the above function. But it doesn't work. Thanks in advance.

Thanks & Regards

Priyanka

Re: Convert negative number value

Anil_Babu_Samineni — Wed, 22 Feb 2017 10:52:28 GMT

Can you eloberate more on this?

Why can't simply Number format with 2 decimals. May be provide with more input values and expected result

Re: Convert negative number value

veeranj — Wed, 22 Feb 2017 10:53:23 GMT

Hi ,

Try using fabs(number) to convert negative to postive values .

Try round(number) to to convert number to the nearest integer or nearest decimal .

round(-1.78965689,10) will give 0 as output .

check round function option .

Thanks,

Veera

Re: Convert negative number value

Frank_Hartmann — Wed, 22 Feb 2017 10:56:17 GMT

try using Round() function or try like this:

num((-1.78967*******e-16),'##.##')

hope this helps

Re: Convert negative number value

sarvesh — Wed, 22 Feb 2017 11:19:06 GMT

Hi..Priyanka,

You can try below mentioned code:

NUM((SUM(profit)/SUM(amount) -1) * 100, '#,##0.0% ;(#,##0.0%)')

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: Convert negative number value

adamdavi3s — Wed, 22 Feb 2017 11:22:24 GMT

It depends on where you are displaying this number?

If in a chart then you should simply be able to alter the display option on the 'number' tab.

If in a textbox or title or something then you will need to use one of the formula mentioned above

Re: Convert negative number value

Wed, 22 Feb 2017 11:23:10 GMT

Hello All,

Thanks for the response. To explain it further, I have a bar chart with many values as below:

-1.78967********e-16

-0.28

-0.31

-1.00

I have formatted the number to show only 2 decimal places. It works fine for all the values except the exponent value. I want to show the exponent(-1.78967********e-16) as 0. so, my data in bar should look like

-0.28

-0.31

-1.00

Hope this explanation helps. Thanks in advance.

Re: Convert negative number value

sarvesh — Wed, 22 Feb 2017 12:22:08 GMT

@piyanka - Please mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: Convert negative number value

adamdavi3s — Wed, 22 Feb 2017 12:24:42 GMT

quite clearly the issue isn't solved?

Re: Convert negative number value

adamdavi3s — Wed, 22 Feb 2017 12:25:33 GMT

Can you simply round your formula in this case?

Round(sum(value),0.01)

Re: Convert negative number value

Wed, 22 Feb 2017 12:33:24 GMT

Directory;

LOAD Id,

Value,

Round(Value,'10') as Result

FROM

Its Working

Re: Convert negative number value

Anil_Babu_Samineni — Wed, 22 Feb 2017 12:41:27 GMT

I am not sure, How your real data seems, But practice it should work

LOAD If(SubStringCount(Wellcome,'e'),0,Wellcome) as Wellcome Inline [

Wellcome

-1.78967********e-16

-0.28

-0.31

-1.00

];

Re: Convert negative number value

adamdavi3s — Wed, 22 Feb 2017 12:46:45 GMT

e-16 means 10 to the power -16 i.e. 16 zeros before the number

Re: Convert negative number value

adamdavi3s — Wed, 22 Feb 2017 12:47:27 GMT

You might also want to check if 'supress zero values' is switched on as this will stop the exponent value showing if Qlik sees it as 0

Re: Convert negative number value

Wed, 22 Feb 2017 14:12:31 GMT

Hello All,

Thanks everyone for your answers. It worked by using the 'Round' function. Thanks @adam davies

Have a nice day

Thanks & Regards

Priyanka