Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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')
thanks swuehl.
my objective is to do it in anexpression.
maybe this is not a thread for New To QlikView?! :))
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
thanks you Stefan.
I will try it out tonight if I have time and will let you know how I get on.
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