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 22% i.e., 330598005 / (330598005 + 1175520565)
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.
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:
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:
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!
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 (email@example.com).
Yep- That worked! I had to change the "Compute Using" fields.