topic Re: Number format in QlikView

Number format

Mahamed_Qlik — Tue, 26 Apr 2016 12:29:06 GMT

Hi all,

How to convert the following amount into two decimal correct number format ?

suppose I have number as 153,050.00 (something like 1,53,050.00)

Regards,

mahamed

Re: Number format

PrashantSangle — Tue, 26 Apr 2016 12:31:13 GMT

Hi,

Use num(FieldName,'#,##,#00.00')

Kind Regards

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 12:31:39 GMT

May be try this:

=Num(Num#('153,050.00', '#,##0.00'), '#,##,##0.00')

or just this for a numeric field

=Num(FieldName, '#,##,##0.00')

Re: Number format

MK_QSL — Tue, 26 Apr 2016 12:32:28 GMT

=Num('153,050.00','#,##,##0.##', '.' , ',' )

Re: Number format

amit_saini — Tue, 26 Apr 2016 12:33:26 GMT

hi,

Try this:

=num(your_Field, '#,##,##0.00')

Thanks,

Re: Number format

swuehl — Tue, 26 Apr 2016 12:39:30 GMT

I don't understand your request.

In what sense is this a correct number format:

1,53,050.00

What is the rule behind using the thousand separator differently?

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 12:41:55 GMT

Stefan -

In India (and may be other parts of the world) we do use a different version of comma separators

For example:

10,00,00,000

Indian numbering system - Wikipedia, the free encyclopedia

Re: Number format

PrashantSangle — Tue, 26 Apr 2016 12:42:47 GMT

Hi,

In Asia People use this kind of format.

Regards

Re: Number format

ThornOfCrowns — Tue, 26 Apr 2016 12:52:32 GMT

That was an interesting read, thanks for the link!

Re: Number format

swuehl — Tue, 26 Apr 2016 13:28:55 GMT

Sunny, thank you for the link, I didn't know this indeed.

But I think we would need to give a full length format code then (e.g. assuming max 14 digits :

=Num(YourExpression, '#,##,##,##,##,##,##0','.',',')

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 13:31:04 GMT

I agree, but do you think there is another way to do this (may be changing or setting some environmental variables)? Wouldn't Qlik know that there is another number format available which might be used?

Re: Number format

Mahamed_Qlik — Tue, 26 Apr 2016 13:44:26 GMT

Hi Sunny,

Num(FieldName, '#,##,##0.00') this is working but what happens is

it is automatically adding .00 to each value

For example if my amount is like 153,050 then it should show as 1,530.50 but it is showing as 153,050.00

Kindly advise

Re: Number format

Mahamed_Qlik — Tue, 26 Apr 2016 13:45:05 GMT

Hi Amit,

It is my dimension value not measure.

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 13:50:02 GMT

I guess try it like this then:

Num(FieldName, '#,##,##,##,##,##,###')

Re: Number format

Mahamed_Qlik — Tue, 26 Apr 2016 13:53:53 GMT

What if I will need upto two decimal ?

Re: Number format

ThornOfCrowns — Tue, 26 Apr 2016 13:55:00 GMT

13 digits gives you 10,00,00,00,00,000 = one lakh crore / ten kharab / one thousand arab = One trillion

That should be enough to be getting on with!

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 13:55:37 GMT

In the above example I see two decimal showing up, Isn't that what you want?

Re: Number format

Mahamed_Qlik — Tue, 26 Apr 2016 14:07:39 GMT

Well sunny,

I will tell the actual issue is detail.

I have one dimension named as GBPRateAmount.

153.20

14.364

1,123.645

246,200.9687

So, I want the result should be like below

153.20

14.36

1,123.64

2,46,200.96

Now tell me

Regards,

Mahamed.

Re: Number format

Mahamed_Qlik — Tue, 26 Apr 2016 14:11:08 GMT

And here is the script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

//SET MoneyDecimalSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mo;Tu;We;Th;Fr;Sa;Su';

SET HidePrefix = '_';

Re: Number format

sunny_talwar — Tue, 26 Apr 2016 14:16:09 GMT

May be this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep='.';

//SET MoneyDecimalSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mo;Tu;We;Th;Fr;Sa;Su';

SET HidePrefix = '_';

Table:

LOAD *,

Num(GBPRateAmount, '#,##,##,##,##,##,###.##') as NewField;

LOAD * Inline [

GBPRateAmount

153.20

14.364

1123.645

246200.9687

];