5 Replies Latest reply: Nov 8, 2011 5:23 PM by rizo98 RSS

    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

        • Re: Number format in espression
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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

                      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

                        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