topic Re: Dividing two variables: decimal comma versus decimal point in QlikView

Dividing two variables: decimal comma versus decimal point

Tue, 04 Mar 2014 17:30:51 GMT

In my script, I have two variables (A1 and A2). I am calculating a 3rd variable (A3) by summing the first two variables (A1 + A2). I am running into problems with decimal points and comma's.

The variables A1 and A2 are both smaller than 1. Although I have set the decimal separator to be comma (',') in my document, I am using a decimal point for A1 and A2. If I don't do this, A1 and A2 are not being recognized as numbers. When I calculate A3, the result has a comma (',') as decimal separator. Any further calculations using A3 go wrong, presumably because A3 with the decimal comma is not being recognized as a number. Can anybody point out how to solve this?

Re: Dividing two variables: decimal comma versus decimal point

Tue, 04 Mar 2014 18:06:29 GMT

Hey Marc,

Would it be possible to attach a sample file with some dummy data and the issue you are facing? It would be very helpful in addressing this.

Also where did you set the decimal separator as comma. You can set it only for specific fields. When you put A1 and A2 in a text box do you see comma or decimal point.

Thanks

Re: Dividing two variables: decimal comma versus decimal point

Anonymous — Tue, 04 Mar 2014 18:12:39 GMT

Marc, try using this, reading A3 with decimal comma using num#(), and converting to decimal dot using num()

num(num#(A3,'#.###,#'),'#,###.#').

Re: Dividing two variables: decimal comma versus decimal point

Tue, 04 Mar 2014 18:16:41 GMT

Attached a sample file similar to what you explained. It seems to be working fine. Please take a look at it let me know.

Edit:

Use these as your environment variables:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='$#.##0,00;($#.##0,00)';

Thanks

Re: Dividing two variables: decimal comma versus decimal point

Tue, 04 Mar 2014 18:24:19 GMT

Hi Michael,

Thanks for the answer. This works.

Amazing how some carppy some of the Qlikview scripting language is:

Even though you set the decimal seperator to comma, varaiables only work with a decimal point
If you do a calculation with the variables, theoutcome has a decimal comma
You need to concert the outcome to a numer with a decimal point to be able to use it for further calculations

Anayway, thanks again.

Re: Dividing two variables: decimal comma versus decimal point

Anonymous — Tue, 04 Mar 2014 18:31:10 GMT

Marc,

I'm glad my suggestions works for you. My purpose was to make it independent from the environment variables settings. But AJ proved in his example that there is no problem using comma. I think something is missing in your situation.

Anyway, whatever it is, you'll be fine.

Regards,

Michael