Perhaps I am over thinking this because it is late, but the problem seems to be from a conflict in the order in which Tableau does the calculations for the sheet. You can easily get to a list of the programs for 2013 Applications>Parameter but in the process you lose all the previous years data in the view.
To get around this you need to create a new column of data populated with the 2013 application numbers in each row of the program regardless of year. In an ideal situation I would use SQL to create this. I'll have to think about how to achieve something similar to the SQL solution inside of Tableau. My first instinct would be overwriting Tableau's calculation, along the line of point 3 here: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html.
If anyone else has ideas, please jump in.
Here's my contribution: When we want the value of one cell (based on a row or set of rows) in a Tableau view to depend on a value of another cell, in Tableau that either calls for a new column in the data like Ashley suggested, or table calculations. What I did was to set up a flag calculation that returns 1 for the any rows that meet the criteria, then table calc that does an IF WINDOW_MAX([the flag calc]) == 1 THEN 1 END. Put that on the Filters shelf and set the Compute Using for that to be Year (so it partitions for each Programme name & code) and you should be all set.
See the workout sheet in the attached for putting together the view, then the result sheet for an optimized version of the table calc in action.
Also, in practice, I might set the "2013/4" calc in the view to use a parameter, so all I'd have to do next year would be to change the parameter and not have to remember any/every calc that was checking for the year.
1 of 1 people found this helpful
Thanks Jonathan, that was exactly where I was trying to get to. I thought about the two components separately, but not together. Going to tuck this into my bag of tricks.
Thanks so much Jonathan - that's perfect!
I've had a chance to look at your solution in more detail. Please see the attached twbx which shows your 'result' sheet with a filter on programme to bring back a small number of rows. This is the only amendment I've made. Adjusting the applications parameter changes the application totals. I'm not sure why this should be?
To recap, the applications parameter should filter the rows based on the 2013/4 values so, for example, if I were to set the parameter to 12, i would expect the History (Grad Cert) to disappear from the but the other rows to remain, with their values unaltered.
Am I missing something here?
I would add that I appreciate your help here.
I believe the solution is to add a calculated field which returns the value of the 'latest year' (final column on the right) via LOOKUP(ZN(SUM([measure name])), LAST()) and then apply the filter to this new measure.
In my example below I have filtered for all programmes where the number of applications is below 7. Note how the filter has only been applied to the final column.