I have attached the updated workbook.
Thanks. I'm not sure if this is the best way to tackle this, but this strikes me as an application for Table Calculations. And, since you are doing % of total calculations already, we'll get to delve into nested table calculations, which is a bit of an advanced, but let's jump in and see where it takes us.
See attached workbook. Here is what I did:
1. Created a calculated field, "Records as % of Gender" which is a table calculation that matches what you already had on the Marks shelf. You'll see why I made it into a calculated field in a minute.
2. Create a calculated field, "Records Last Year", which is a simple LOOKUP() of "Records as % of Gender". The LOOKUP() function allows us to sneak a peek at values that appear elsewhere in our view. In this case, LOOKUP ( [Records as % of Gender], -1) allows us to look one spot "behind" where we're at now. (What constitutes "behind" will depend on how we configure the table calculation when we place it in the view. We'll come back to that in a second.
3. Create one more calculated field, "Difference from Last Year", which is just a straight subtraction — with a check to ensure that null values get converted to zeros so the subtraction can happen. (However, because it is based on table calculations, it will need to be configured the way any other table calculations are configured. We'll do that once we place it in the view.)
4. Drag "Measure Values" to your Marks card as a label. This will cause all of your Measures to show up in a new "Measure Values" shelf. Remove everything except the 3 calculated fields we created above ("Records Last Year", "Records as % of Gender", and "Difference from Last Year"). Drag them around to put them in the order you want to see them in the columns.
5. Click on "Records Last Year" and select Edit Table Calculation
6. This field uses Nested Calculations. That means there are two levels of table calculations going on here — the "% of total" over gender for a single year, and the LOOKUP() to access the previous year's result. This is an advanced concept (in my opinion) that can be difficult to conceptualize, but it allows you to do a lot of REALLY COOL STUFF™ in Tableau. Select each field under the Nested Calculations drop-down, and make sure it is configured correctly. "Records as % of Gender" should be configured to Specific Dimensions (with Gender selected in the list of dimensions). "Records Last Year" can be configured as simply Table (Across).
7. Click on "Difference from Last Year", select Edit Table Calculation, and configure it the exact same way as above — with the "Difference from Last Year" nested calculation being set to Table (Across).
8. Drag Measure Names to the Columns shelf, as the last pill. At this point, you should have several columns for each year. (You had Subtotals enabled for Title. You'll want to turn those off.)
9. Right-click on one of the headers "2014" and choose "Hide". Now you'll only see 2015, but the cool thing about Table Calculations is that they can sneak a peek at data that isn't even visible. As long as the data is there, but hidden, it can be pull in on a table calculation.
10. Click on "Measure Names" (in the Columns shelf) and select "Edit Aliases…"
11. Find the field names that correspond to the column headers you see for each of your Measure Values, and change them to something that will make more sense. You may notice multiple versions of the field; this tends to happen if multiple table calculation configurations have been attempted (like I did when experimenting!). You may have to use trial and error to find out which one is the one you want to alias. In the below screenshot, the fields with an asterisk * are the ones I changed.
12. Click the Year pill on the Columns shelf and deselect "Show Header"
13. Now your sheet should look all pretty!
I hope this helps!
Percent-over-prior-year.twbx 27.8 KB
Thanks a lot for giving this detailed explanation. The final image is the result which I am trying to achieve. I am currently using Tableau 9.3 and can I perform nested calculations on that ? Currently, I am not able to progress after step 6.
The Table Calc configuration interface changed drastically in 10.x, which is what I'm using. However, the functionality was there in 9.x.
I did a quick Google Image search to try to find a 9.x view of nested table calcs, and I found this blog article. It may help you translate the 10.x screenshots in the equivalent 9.x configuration. (WARNING: Configuring table calcs in 9.x was vastly more confusing than in 10.x. They really polished things up in 10.x.)
Shameless paste of the key image that led me to that article:
I am still not able to reach to the nested calculation part. When I try to do that through edit table calculation, I am getting only two options in the drop down (Table(Across), cell).
Your final result is exactly the one I wanted, but I am not able to reach there because of this. I would really appreciate your help.
1 of 1 people found this helpful
Yay, I found my copy of Tableau 9.3 lying around.
Most likely, when you created "Records as % of Gender", you did not specify a Default Table Calculation. I sort of glossed over that in my instructions, but what I originally did was take your "SUM([Number of Records])" table calculation on the Marks shelf and dragged it right into the Measures to auto-create a calculated field. In doing so, it also set up my Default Table Calculation to match what you had already configured on the Marks shelf.
So here's what you do now to get it aligned:
Edit the calculated field "Records as % of Gender" and click the blue "Default Table Calculation" at the bottom-right.
Select "Gender" in the "Compute Using" dropdown, and hit OK.
Then go back to your "Difference from Last Year" pill, go to Edit Table Calculation, and see if it shows you the right configuration for that level of the nested calculation.
See if that does the trick!
Thanks a ton for your great help. I am able to get the desired output
Thank you once again
Based on your screenshot, it looks like you don't have any dimensions in your view. Table calculations compute along dimensions, so if you don't have any dimensions in the view then (1) you won't see any options for specific dimensions to compute along, and (2) in all likelihood your table calculations won't work as expected, because the necessary dimensions are not present.
This is the double-edged sword that is table calculations. They can traverse data in ways that no other mechanism can, but they can only navigate details that are actually in the view. Satisfying this requirement, while still displaying exactly what you want the user to see, can sometimes necessitate tricky view constructions.