topic Re: Division Issues in QlikView

Division Issues

Tue, 06 Jan 2015 13:23:37 GMT

Hello Everybody,

I am facing issue in division and decimal values.

In a text file I tried
=(-3948.41/-39484.08)*100 and the result is 10.000005065333

But if you try the samething manually in calculator you will get the result 10.0000050653327

The thing is QlikView is rounding off the last two values i.e 27 to 3.

Could any one please tell me how to get the correct value if we calculate it mannually .

Thanks In Advance

Sonali

Message was edited by: Sonali Singh

Re: Division Issues

marcus_malinow — Tue, 06 Jan 2015 13:26:19 GMT

use the num function to round to as many decimals as you require

e.g.

num(

(-3948.41/-39484.08)*100

,'#.000000000000000')

Re: Division Issues

Tue, 06 Jan 2015 13:27:49 GMT

HI Sonali,

If it is in dash borad you need to check the property called Sort in that you can change how many decimals you need.

Re: Division Issues

marcus_malinow — Tue, 06 Jan 2015 13:28:15 GMT

n.b with 15 dp, as in my example above, you'll get a result of 10.000005065332660

Re: Division Issues

Tue, 06 Jan 2015 13:34:16 GMT

Ya correct I think Num function works upto 14 decimal places.

I tried the same upto 14 decimals =num((-3948.41/-39484.08)*100,'#,##0.00000000000000') and got 10.00000506533266

and if you give 15 decimals =num((-3948.41/-39484.08)*100,'#,##0.000000000000000') gives 10.000005065332660 but what I want is 10.0000050653327

Re: Division Issues

marwen_garwachi — Tue, 06 Jan 2015 13:39:37 GMT

Try this :

=num(((-3948.41/-39484.08)*100),'############ ###')

Ps: you can also put this format (############ ###) in Numbers format panel.

Hope it will help you.

Marwen

Re: Division Issues

Tue, 06 Jan 2015 13:43:31 GMT

Thank you very much... It's working

Re: Division Issues

marcus_malinow — Tue, 06 Jan 2015 14:07:51 GMT

So try to 13 decimals

You're welcome

Re: Division Issues

Tue, 06 Jan 2015 14:21:31 GMT

HI Marwen,

still some issue is there.

If I am trying =num((546.42/1787.66)*100,'###############') in text object I am getting the result 30.5662150520793 but if I am using the same thing in the script I am getting the result as 30.566215052079.

The same Num function works differently in script and in text object.

What I want is if I apply the same thing in script and load this then also the result should be 30.5662150520793.

Please advice.

Re: Division Issues

marwen_garwachi — Tue, 06 Jan 2015 14:32:22 GMT

can you publich a sample file ?

Regards

Marwen

Re: Division Issues

Tue, 06 Jan 2015 15:09:03 GMT

Hi Marwen,

I have attached the sample. Could you please look into this and tell me why the same Num() works differently in text object and in the loaded scipt.

The field name is Risk1%.

Thanks

Sonali

Re: Division Issues

marwen_garwachi — Tue, 06 Jan 2015 16:01:04 GMT

The result should be 30.5662150520792 (with 13 decimals) or 30.56621505207925 (with 14 Decimals) .

Here's the Qvw File attached.

Re: Division Issues

Wed, 07 Jan 2015 07:39:37 GMT

Hi Marwen,

Thanks for the reply.

My requirement is something different.

The requiremnt is the sum of risk1% should be exact 100.

30.5662150520793+37.4567870847924+31.9769978631283= 100

But if you calculate 3,056.62150520792+3,197.699786312833+745.67870847924=9,999.99999999999

In QlikView if I am doing the partial sum it's giving me the total as 100.

But as I am storing into a text file there it's not rounding off the value and it's giving the result as 9,999.99999999999.

Please advice.

Thanks

Sonali

Re: Division Issues

marwen_garwachi — Wed, 07 Jan 2015 09:20:14 GMT

I'm Sorry Dude, but i've Checked again and the result was always 100

Check the sample file joined .

Regards

Marwen

Re: Division Issues

Wed, 07 Jan 2015 09:41:34 GMT

Thanks for your time.

what you have done is correct but if you add the field 'Risk Code' and then if you calculate the sum the result will not be 100.

Policy	Policy Section	Policy Version	Risk Code	sum(Risk%)	Sum(Risk1%)
DX1439061	1	4	AP	30.566215052079	3056.6215052079
DX1439061	1	4	L3	31.976997863128	3197.6997863128
DX1439061	1	4	H3	37.456787084792	3745.6787084792
				99.999999999999	9999.9999999999

Re: Division Issues

marwen_garwachi — Wed, 07 Jan 2015 10:12:27 GMT

Strange ! in my sample File it gives 100

Excel Check :

sum(Risk%)	sum(Risk1%)
30,5662150520792	3056,6215052079200
37,4567870847924	3745,6787084792400
31,9769978631283	3197,6997863128300
100,0000000000000	10000,0000000000000

Re: Division Issues

Wed, 07 Jan 2015 10:26:24 GMT

Hi,

As I told you if you use partial sum or total in the chart properties it's giving total as 100 but if you calculate it manually it's not 100.

305,662,150,520,792+374,567,870,847,924+319,769,978,631,283=999,999,999,999,999