6 Replies Latest reply on Jun 30, 2014 11:07 AM by Matt Lutton

    Row Level Formatting - Adding Signs

    Ankit Patel

      Fellow Users,

       

      I am trying to achieve Row Level formatting for attached view. I want to add $ & % sign where applicable next to the number on both columns


      To Date Net Sales $ --- should display the actual value as $111,222 under Current Month and $3,333,444 under Current Year ($ signs)


      % of To Date Budget --- should display the actual value as 777% under Current Month and 888% under Current Year (% signs)



      How do I achieve this?

        • 1. Re: Row Level Formatting - Adding Signs
          Don Barnetson

          Hi Ankit,

           

          You can use a case formula that looks something like this: (use the same for [Current Year])

           

          case [Period]
          when "% of To Date Budget" then str([Current Month])+"%"
          when "% of Full Period Budget" then str([Current Month])+"%"
          when "To Date Net Sales $" then "$"+ Left(str([Current Month]),3) +"," + right(str([Current Month]),3)
          when "To Date Budget $" then "$"+ Left(str([Current Month]),3) +"," + right(str([Current Month]),3)
          when "Full Period Budget $" then "$"+ Left(str([Current Month]),3) +"," + right(str([Current Month]),3)
          end

           

          I've attached a revised version of your example.

           

          Hope this helps.

          Don

          • 2. Re: Row Level Formatting - Adding Signs
            Robert Sutter

            You can also call rawsql and use the JetSql format function to return the desired format.

             

            When formatting the field in Tableau, Use the default Custom format and leave the format mask blank.

             

            You may also be able to pre-format the values in excel and use the Custom format to allow the default format to pass through. I've done it with number of decimal places successfully so it may be easier.

            • 3. Re: Row Level Formatting - Adding Signs
              Ankit Patel

              Don Barnetson

              There is a problem with this - on the "Current Year $ vs %" calculation the amount is in million - hence there should be million and thousands separator. i.e. 3333444.00 should be displayed as 3,333,444.00 so forth - I tried using LEFT, MID, RIGHT to get this to work but not able to achieve it?

               

              Any thoughts on how to get this done?

              • 4. Re: Row Level Formatting - Adding Signs
                Matt Lutton

                Its been a long time since I've used this, but you're welcome to see if this helps you at all:
                Number Formatting with Comma Separator inside Parameter Calculation

                • 5. Re: Row Level Formatting - Adding Signs
                  Ankit Patel

                  This wont work because I have multiple rows with different formatting - i.e. see below:

                   

                  case [Period]

                  when "% of To Date Budget" then str([Current Year])+"%"

                  when "% of Full Period Budget" then str([Current Year])+"%"

                  when "To Date Net Sales $" then "$"+ Left(str([Current Year]),3) +"," + right(str([Current Year]),3)

                  when "To Date Budget $" then "$"+ Left(str([Current Year]),3) +"," + right(str([Current Year]),3)

                  when "Full Period Budget $" then "$"+ Left(str([Current Year]),3) +"," + right(str([Current Year]),3)

                  end

                  • 6. Re: Row Level Formatting - Adding Signs
                    Matt Lutton

                    My point was that the formatting used in that example goes up to 100 trillion, I believe.

                     

                    As an example, for Sales:

                     

                    when "Sales" then

                    IF len(str([Sum Sales]))<3 THEN

                        "$" + str([Sum Sales])

                    ELSEIF len(str([Sum Sales]))<=6 THEN

                        "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-3)

                        + "," + RIGHT(str([Sum Sales]), 3)

                    ELSEIF len(str([Sum Sales]))<=9 THEN

                        "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-6)

                        + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                        + RIGHT(str([Sum Sales]), 3)

                    ELSEIF len(str([Sum Sales]))<=12 THEN

                        "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-9)

                        + "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

                        + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                        + RIGHT(str([Sum Sales]), 3)

                    ELSEIF len(str([Sum Sales]))<=15 THEN

                        "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-12)

                        + "," + MID(RIGHT(str([Sum Sales]), 12), 1, 3)

                        + "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

                        + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                        + RIGHT(str([Sum Sales]), 3)

                    ELSE

                        "More than 100 Trillon Dollars"