topic Re: Text object expression not displaying correctly in QlikView

Text object expression not displaying correctly

Mon, 26 Jan 2015 20:15:05 GMT

Hi,

When using the following expression in a text box the displayed field is not as expected:

=Num(Sum ([# Transported Passengers]) / 1000000, '#,##0.00')

Current display:

Should be:

Is it my document settings?

Thank you for helping out

Hein

Re: Text object expression not displaying correctly

Colin-Albert — Mon, 26 Jan 2015 20:24:18 GMT

What is displayed if you just show

Sum ([# Transported Passengers]) / 1000000

What version of Qlikview are you using? Version & build number?

Re: Text object expression not displaying correctly

Anonymous — Mon, 26 Jan 2015 22:07:03 GMT

did you try:

=Num(Sum ([# Transported Passengers]) / 1000000, '###,###.#0')

also, what do your set statements look like for thousand separator and decimal symbol? (first few lines in your script)

Re: Text object expression not displaying correctly

MarcoWedel — Mon, 26 Jan 2015 23:33:17 GMT

Hi,

it's your thousand and decimal seperators.

i.e.:

SET ThousandSep=',';
SET DecimalSep='.';

delivers the expected result:

while

SET ThousandSep='.';
SET DecimalSep='.';

delivers

Although it would be best to correct the settings in the script, you could adjust your text box expression like this:

=Num(Sum ([# Transported Passengers]) / 1000000, '#,##0.00', '.', ',')

hope this helps

regards

Marco

Re: Text object expression not displaying correctly

Tue, 27 Jan 2015 01:50:22 GMT

Both options worked.

1) updated my load script to

SET ThousandSep=',';

SET DecimalSep='.';

Reloaded data and changed # Transported Passengers setting to Integer

2) adjusted my text box expression to this:

=Num(Sum ([# Transported Passengers]) / 1000000, '#,##0.00', '.', ',')

Thank you all for your inputs.

Re: Text object expression not displaying correctly

Tue, 27 Jan 2015 02:03:24 GMT

PS. How do you update global settings to always show thousand separator as ',' when creating a new QV document?

The default load script SET ThousandSep=' ';

and it should be SET ThousandSep=',';

Re: Text object expression not displaying correctly

jagan — Tue, 27 Jan 2015 02:44:42 GMT

Hi,

Copy this into a txt or qvs and include it in the qlikview file like below

//Load Script.txt

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

// Script to include in Qlikview file
$(Include=C:\Load Script.txt)

Hope this helps you.

Regards,

Jagan.

Re: Text object expression not displaying correctly

Tue, 27 Jan 2015 02:51:57 GMT

Hi Jagan,

Thank you, does it mean that I cannot change my global settings and have to load script every time I create a new document?

Kind regards

Hein Frohnapfel