Formatting trouble once again

[datanibbler]

Hi,

I have some trouble again with numeric variables.

I have to divide one variable through another (to get an average of costs).

The issue is, in the table where one of these variables comes from (using the PEEK() function) I have formatted that in the German format with '#.##0,00'.

<=> Internally I guess QlikView uses the '.' as decimal_separator. what I know is that dividing this through another variable (an integer) does not work.

=> I tried to replace the ',' with the '.' to make it usable, but I don't quite know how to do that in the script - it doesn't work yet. I guess QlikView misinterprets the ',' as a parameter_separator. On the GUI, it's no issue at all to divide the one through the other, but in the script ...

Can someone help me there?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I'm just trying reformatting the field with the '.' as decimal_separator, that might work - but the variable is also used in another calculation and I have to see if that still works ...

[swuehl]

Have you tried with a numeric expansion:

$(# variablename )

[giakoum]

did you try using the num# function?

[tresesco]

In the script:

SET DecimalSep='.';

?

[datanibbler]

Hi swuehl,

sure I have. I always need that in the script, no?

However, QlikView doesn't recognize the number as such, I guess it's because of the ',' which QlikView interprets as parameter_delimiter. Therefore I cannot even use the REPLACE() function.

When I use it from the start like

>> LET v_myvar = REPLACE(PEEK('fieldname'), ',', '.') <<

=> then the number reads like >> 103.500.37 << - it now has the '.' both as thousand_separator and as decimal_separator. I'm sure that won't work ...

[datanibbler]

Hi tresesco,

I will try that.

Actually, that SET command is on the "Main" tab and the comma is my default - so I don't quite understand why QlikView doesn't properly recognize the comma as decimal_separator?

I will try with num# also.

[swuehl]

Do you really tried a numeric dollar sign expansion:

Let vA = num(1.5 ,'##0,00',',','.');

Let vB = num(2.3 ,'##0,00',',','.');

TRACE $(vA) / $(vB); // Would result in 1,50 / 2,30 ... error

TRACE $(#vA) / $(#vB); // Works for me: 1.5 / 2.3

[datanibbler]

Hi,

I am not quite sure how to use the num# function - the help_file is not particularly helpful in that respect.

Anyway, the variable is seemingly recognized fine - in an Inputbox I can display it as

>> 103.500,37 << (this is the variable v_myvar and it is numeric)

But I cannot divide that through another variable - the formula is like

>> num(($(v_myvar) / $(v_othervar)), '#.##0,00') <<

Then I get an error_message about the formula. That isn't particularly helpful, either - it always says ") expected" - the figures are both properly recognized judging from the log - but then it says "Error in expression - ')' expected"

[datanibbler]

Hi swuehl,

those are a bit too many commas - I don't get it.

I always use the num() function like

>> num([number], '#.##0,00') <<

My decimal_separator and thousand_separator are both set on the "Main" tab as ',' (decimal) and '.' (thousand). Why do I have to specify those again in the num() function? I have a complete formatting_code in there, no?

[swuehl]

This is only to demonstrate the use of numeric dollar sign expansion on a formatted variable

The important part is

$(# vVariable)