topic Number Format in QlikView

Number Format

bharatkishore — Tue, 08 Mar 2016 08:05:57 GMT

Hi All,

I have given the expression as

sum(Amount).

I am getting the value as 3768579921.16

Now i need to get value as integer with dollar symbol and i have given expression as

num(Sum([LC2 Amount]),'$ #,##0').

After giving this expression i am getting the value as $3,768,579,921

But now my requirement is in need to get the above value as

$37.16(round with two decimals and numbers in millions).

Hope you got it..

Num Format

Re: Number Format

its_anandrjs — Tue, 08 Mar 2016 08:10:25 GMT

Try this

=Round( num(Sum( 3768579921.16),'$ #,##0')/100000000, .01)

Regards

Anand

Re: Number Format

bharatkishore — Tue, 08 Mar 2016 08:13:09 GMT

Hi Anand,

Tried your solution but not able get my requirement..

Re: Number Format

its_anandrjs — Tue, 08 Mar 2016 08:15:07 GMT

how you get 37.16 what is the calculation.

Regards

Anand

Re: Number Format

nagireddy_qv — Tue, 08 Mar 2016 08:17:04 GMT

try with money() function..and just the values in the number format tab...

Re: Number Format

bharatkishore — Tue, 08 Mar 2016 08:17:16 GMT

Sorry Anand.. I got the solution what you have given is correct.. But i need to display dollar symbol also...

Re: Number Format

kaushiknsolanki — Tue, 08 Mar 2016 08:22:08 GMT

Hi,

Try this.

num(Floor(sum(Amount)/1000000) + (sum(Amount) - Floor(sum(Amount)) ),'$ #,##.00')

Regards,

Kaushik Solanki

Re: Number Format

its_anandrjs — Tue, 08 Mar 2016 08:22:48 GMT

Try this with money function

=Money( Round( num(Sum( 3768579921.16),'$ #,##0')/100000000, .1),'$ #,##0.00')

Regards

Anand

Re: Number Format

bharatkishore — Tue, 08 Mar 2016 08:25:53 GMT

Everything is fine except the total. Here the "total values" are coming as 339.56 and remaining comes as 34.12. Is it possible to change the "total values" as 33.56

Re: Number Format

kaushiknsolanki — Tue, 08 Mar 2016 08:33:30 GMT

Hi,

Try the one which I gave you.

Regards,

Kaushik Solanki

Re: Number Format

bharatkishore — Tue, 08 Mar 2016 09:58:42 GMT

Hi Kaushik,

My expression is

=Money(Round(num(Sum({<[Ageing Buckets]={"Current"},[Clrng doc]={"*"}>}[LC2 Amount]),'$ #,##0')/1000000, .01))

Is there any modifications i need to do

Re: Number Format

kaushiknsolanki — Tue, 08 Mar 2016 10:07:16 GMT

Hi,

It will be as below.

num(Floor(Sum({<[Ageing Buckets]={"Current"},[Clrng doc]={"*"}>}[LC2 Amount])/1000000) +

(Sum({<[Ageing Buckets]={"Current"},[Clrng doc]={"*"}>}[LC2 Amount])

- Floor(Sum({<[Ageing Buckets]={"Current"},[Clrng doc]={"*"}>}[LC2 Amount])) ),'$ #,##.00')

Regards,

Kaushik Solanki

Re: Number Format

bharatkishore — Tue, 08 Mar 2016 10:12:48 GMT

Thanks a lot Kaushik