1 of 1 people found this helpful
Table calc filters and the grand total are on separate Tableau "calculation paths." So the Grand Total path doesn't know anything about the Table calc filter.
The easiest might be to put the table calc filter in the SUM(Amount) measure:
Amount sum =
IF LAST() == 0 THEN SUM([Amount]) END
Thanks, Jim. Any way to get the empty columns to go away now?
Other than right-clicking "hide" on those columns, you mean?
Again, the problem is that getting the last month requires a table calc filter and grand total is not aware of this filter.
My first thought is to use a custom grand total calculation using custom SQL. The basic strategy is to modify the data connection with a UNION ALL so that you have a "Main" and "Totals" set of data. In attached sample, I did this on a duplicate data source---the main risk with custom SQL GTs is that you now have twice as many rows in your data source and every view needs to be filtered on source=main. You can avoid this by using a duplicate connection for views that require this GT workaround.
Then you can create a calculated dimension for Type that returns Type with looking the main rows and "Grand Total" for the Totals row. After adding source and Type GT to the view, you can just drop Amount on the view and use your keep last filter.
There's a step-by-step description of how to do this here:
Total not excluded_jimw.twbx.zip 20.1 KB
In a month from now there'll be another column to hide and no-one to right-click. Thanks for the help though, I might need to look at your recommendations at a later stage; for now I just need the last month in every quarter so it's simple enough to solve in another way (datepart('month',[Date]) = 3,6,9 or 12).
Cool. That's a nice solution.