1 Reply Latest reply on Oct 30, 2013 10:12 AM by Tracy Rodgers

    Formatting numbers from a String Calculation

    Sarah Gehrke

      I am using the following calculation to create a label for my data. However, the number calculation [STR(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0)] is returning numbers with no format (i.e. 3493284020). I would like to format those numbers by adding thousands commas so that number would read as 3,493,284,020. Does anybody know how I would edit the calculation below to achieve that?

       

      Please keep in mind that I have a broad range of numbers from 0 to several million

       

      attr ([SubscriberNames]) + "; " +  STR(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0) + "; " + [Label %]

       

      Thank you!

        • 1. Re: Formatting numbers from a String Calculation
          Tracy Rodgers

          Hi Sarah,

           

          Sorry for the delay in receiving a response! In order to get the format with the commas, you'll need to make the calculate very specific similar to the following:

           

          attr ([SubscriberNames]) + "; " +(if ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0)>9999 then left(str(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0), 2) + "," + mid(str(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0), 3, 3) + " ; " + [Label %]

          elseif ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0)>999 then left(str(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0), 1) + "," + mid(str(ROUND(LOOKUP(sum([SubscriberValues]),0),0)+ 0), 2, 3) + " ; " + [Label %] end)

           

          Hope this helps!

           

          -Tracy

          3 of 3 people found this helpful