Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Number format in espression

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Number format in espression

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

5 Replies
MVP
MVP

Re: Number format in espression

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')

Not applicable

Number format in espression

thanks swuehl.

my objective is to do it in anexpression.

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

MVP
MVP

Re: Number format in espression

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

Not applicable

Re: Number format in espression

thanks you Stefan.

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

Not applicable

Re: Number format in espression

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

Community Browser