4 Replies Latest reply on Apr 3, 2013 8:35 AM by Lauren MacMillan

    How do I format a calculated field as a %?

    Lauren MacMillan

      Hi,

       

      I am trying to show the % of spend growth associated with each of two segments. Spend growth is just defined to be the change in spend from 2004 to 2011 in the case shown in the image below. I was able to create a calculated field "Spend Difference" that shows the change from 2004 spend for each year shown, though the only one I care about is for 2011. The formula for the field is "ZN(SUM([Spend])) - LOOKUP(ZN(SUM([Spend])), FIRST())."

       

      I would like to be able to show this as a percent, but it seems like I can't format this as a percent of the column. Is there a way to either make another calculated field or just format this field as a %.

       

      Thanks!

      Lauren

       

      Capture.PNG

        • 1. Re: How do I format a calculated field as a %?
          Jim Wahl

          Hi Lauren,

           

          A couple of ideas.

           

          Display Last Date Only

          If you only want to show the last value (2011 growth), you could just right-click on the other years and select "Hide."

           

          If your view is going to change frequently, you could also do this with a table calc.

          • Add the following IF statement to your current table calc: IF LAST() == 0 THEN ... END. Now all values except the last will be NULL.
          • Move Year to the level of detail.
          • For Tableau 8, turn off stacked marks to eliminate white space. Analysis > Stack marks > Off
          • Create a new calculated field for Display date --- IF LAST() == 0 THEN MAX([Year]) END --- after entering this, before closing the window, click on the blue text in the upper right "Default Table Calculation" and select Year. Finally, add this new field to the columns shelf.

           

          Calculating % of Total

          If I understand correctly, you want to replace (or amend) the numbers in each column with a % total for that column.

           

          For 2011, you change

          Hispanic           330,598,005          

          Non-Hispanic 1,175,520,565

          to

          Hispanic           22%    i.e.,  330598005 / (330598005 + 1175520565)

          Non-Hispanic   78%

           

          I'd do this by adding another another table calculation that uses WINDOW_SUM, but is computed using Segment. The Compute Using is the key to table calcs. In your first calc, the Compute Using is by default Table Across, which in this case is Year.

           

          First, create the table calc:

          Spend Difference % = [Spend Difference] / WINDOW_SUM([Spend Difference])  ---- again, after entering this formula, I'd click on "Default Table Calculation" and select "Segment". (Now when you add the measure to the view, you shouldn't have to select Compute Using.)

           

          Now you can add this to the Text box.

           

          Even if the values are correct, it's worth looking at the compute using field, especially since this is a nested table calc. Right-click on the pill > Edit table calculation. At the top of the dialog box, you'll see Calculated Field with a pull-down menu, which means there are multiple table calcs (nested).

           

          Select Spend Difference and you should see Table Across (again, you should be able to change this to Year for the same effect). This means the spend difference is calculated along the Year partition.

           

          Select Spend Difference % and you should see Segment. This means the WINDOW_SUM in this table calc is operating over the segment.

           

           

          Let me know if this doesn't work or isn't clear. If you want to post a sample packaged workbook (twbx) with your data, it should be easy to troubleshoot.

           

          Jim

          • 2. Re: How do I format a calculated field as a %?
            Lauren MacMillan

            Hi Jim,

             

            This is really helpful- thank you!!! I just had a couple of things I wanted to follow-up on. I took your recommendation on making all but the last year show nothing, to make it more flexible. So I updated the Spend Difference Calculated Field with the IF...THEN statement you recommended, which worked fine. But I can't seem to get the Display Date Field to work correctly. I turned off the Stack Marks and that works, as shown below:

            Display_Date_1Capture.PNG

            Then when I add the new Display Data Calculated Field to the Columns Shelf, I see Null and 2011, but then there are 0s in the cells:

             

            Display_Date_2Capture.PNG

            When I just show year, but the Spend Difference and Spend Difference % show up correctly, so I think I missing something small on the Display Date Field, maybe just a formatting change?

             

            Thanks again for your help!

             

            Lauren

            • 3. Re: How do I format a calculated field as a %?
              Jim Wahl

              I'm not sure what's happening, but Spend difference % should be formatted as a percentage. Right-click on the pill > Format, select Pane and in the Numbers setting on the left, choose %.

               

              The other things to check are the compute using fields for each table calc. pill. On the arrow on each pill, select

              for Display date > Compute using > Year

              for Spend difference > ... > Year

              for Spend difference %, you'll need to select Edit table talc and then on the Calculated Field menu,

                    for Spend difference > Year

                     for Spend difference % > Segment

               

              One other thought is that you don't need the IF LAST() logic for the Spend difference %.

               

              If this still doesn't work, perhaps you could post the twbx or sent it to me via email (jimmwahl@gmail.com).

               

              Jim

              • 4. Re: How do I format a calculated field as a %?
                Lauren MacMillan

                Yep- That worked! I had to change the "Compute Using" fields.

                 

                Thanks again!