topic Number format in espression in QlikView

Number format in espression

Tue, 08 Nov 2011 12:15:53 GMT

hello guys

I have the following expressions in 2 list box headings.

They both work but do not display in the format i want them to show. The syntax below could be wrong as i just wrote them from the top of my head.

='Volume - '& Sum(TOTAL vol)]) &' / '& Sum({1}TOTAL vol)

='Share - '& Sum(TOTAL vol)]) / Sum({1}TOTAL vol) * 100

2 questions

How can I show Volume in e.g. 10 milion / billion rather than 10+ digits

How can I show the Share percentage in % rather than its current 0.15etc

I think I need to use the below function. dont i?

But will I be able to display Volume in e.g. 5 milion, billion or trillion (i am using volumes that total upto a trillion)

num'###,###,##0'),

num('#,#0.0%'),

thanks

Re: Number format in espression

swuehl — Tue, 08 Nov 2011 13:04:04 GMT

I don't think that there is a format code for that (or maybe I just don't know. In some objects, there is something like you want, e.g. with the number formatting in bar chart objects).

A general expression could probably look like:

=num( sum(amount) /pow(1000,floor(log10(sum(amount))/3)),'##0.000')

& pick(floor(log10(sum(amount))/3)+1,'',' K',' M',' G',' T')

where you should replace sum(amount) by your actual number calculation you want to format and ' K', ' M' etc. could be replaced by ' thousand', ' million' ... or whatever you like.

Hope this helps,

Stefan

edit: if your sum could be negative, you probably need something like

=num( sum(amount) /pow(1000,floor(log10(fabs(sum(amount)))/3)),'##0.000')

& pick(floor(log10(fabs(sum(amount)))/3)+1,'',' K',' M',' G',' T')

Number format in espression

Tue, 08 Nov 2011 14:19:12 GMT

thanks swuehl.

my objective is to do it in anexpression.

maybe this is not a thread for New To QlikView?! :))

Re: Number format in espression

swuehl — Tue, 08 Nov 2011 14:36:24 GMT

So maybe like this:

='Volume - '&

num( sum(total vol) /pow(1000,floor(log10(fabs(sum(total vol)))/3)),'##0.000')

& pick(floor(log10(fabs(sum(total vol)))/3)+1,'',' thousand',' million',' billion',' trillion')

&' / '&

num( sum({1} total vol) /pow(1000,floor(log10(fabs(sum({1} total vol)))/3)),'##0.000')

& pick(floor(log10(fabs(sum({1} total vol)))/3)+1,'',' thousand',' million',' billion',' trillion')

Hope I haven't made a typo now.

And I just reread you OP and saw your second request:

='Share - '& num(Sum(TOTAL vol)]) / Sum({1}TOTAL vol),' ##0.0%')

Hope this helps,

Stefan

Re: Number format in espression

Tue, 08 Nov 2011 14:59:08 GMT

thanks you Stefan.

I will try it out tonight if I have time and will let you know how I get on.

Re: Number format in espression

Tue, 08 Nov 2011 22:23:59 GMT

good news.

It worked.

I had to tag Vol as [vol] and it did the trick.

here are the syntax.

='Vol - '& num( sum(total [Vol]) /pow(1000,floor(log10(fabs(sum(total [Vol])))/3)),'##0.000') & pick(floor(log10(fabs(sum(total [Vol])))/3)+1,'',' thousand',' million',' billion',' trillion')

&' / '&

num( sum({1} total [Vol]) /pow(1000,floor(log10(fabs(sum({1} total [Vol])))/3)),'##0.000') &

pick(floor(log10(fabs(sum({1} total [Vol])))/3)+1,'',' thousand',' million',' billion',' trillion')

='Share = '&num(sum( [Vol]) / sum({1} total [Vol]),'##0.0%')

many thanks Stefan