I don't believe you can't do what you're wanting to do.
I am trying to hide all values shown in the last column(Ads) and display only the total rows or any rows of my choice including the totals.
Gallop, I really don't think this can be done in Tableau.
Can we try with index() ?
case indEX() when 2 THEN Ads
when 4 THEN Ads
This works on another workbook by hiding values in index() 1 and 3.
If I understand your goal correctly, then you have a moderately difficult issue to hide a column in subtotal, which is further complicated by the fact you want to hide / show rows that are used to compute this subtotal.
Hiding a column in subtotal is relatively easy, once you realize that subtotal works by ignoring the right-most dimension, in your case cnt. You can create a calculated field to check for this:
search with blank subtotal =
IF min(cnt) == max(cnt) then min(cnt) // process value normally we're not at the subtotal row
ELSE NULL // we at subtotal, since min != max, and
// you can add a custom calculation here or in your case
// return NULL to to hide it.
By replacing your measures with the new "with blank subtotal" measures you get something like:
But you also want to hide the cnt rows. If you just wanted to hide a few, it would be a lot easier, since you could use a table calc filter (A Jedi (Filter and Table Calc) Trick | Tableau Software). But since you want to hide all of the rows, something more sophisticated drastic is required.
I was able to do this using custom SQL to create two sets of the data: a "Data" set and a "Totals" set. Basically you just select the data source > Edit connection > Custom SQL. Copy and everything in the window, adding the field [Data Source] to each group and an UNION ALL statement between them. It looks something like the below (note that you need to add a comma after the second to last line in the SELECT):
SELECT [Sheet1$].[brand] AS [brand], [Sheet1$].[C] AS [C], ... [Sheet1$].[Total C] AS [Total C], "Data" AS [Data Source] FROM [Sheet1$] UNION ALL SELECT [Sheet1$].[brand] AS [brand], [Sheet1$].[C] AS [C], [Sheet1$].[C Change] AS [C Change], ... [Sheet1$].[Total C] AS [Total C], "Total" AS [Data Source] FROM [Sheet1$]
After you've added this new data source (or edited your existing data source), create a new worksheet with the same fields as you had previously.
Next, add the Data Source dimension to the view betwe brand and cnt. This will create two sets of everything. This makes since, you doubled the number of rows in the data source.
We want the second set of data to be a "Total," so create a new field: cnt header
IF [Data Source] == "Data" THEN STR([cnt]) ELSE "Total" END
Note that you also need to convert cnt to a string, since "Total" is a string.
Replace the current cnt pill on the rows shelf with this new field. The Ads column total is probably not working, because of the table calc compute using, but we'll fix that in a minute.
Next we want to hide the measures in the total column. Similar strategy as above, but we'll just check the data source:
search (hide in subtotal)
IF [Data Source] == "Data" THEN [search] END
Do this for both the search and imp measures and replace them in the view. Now these fields should be hidden in the total row.
The last trick is to get your table calc Ads working. I don't understand what this calc represents, but if you want the total row to be the sum of the rows, you'll need to
1. Edit Ads so that it only operates on the Data source
IF MIN([Data Source]) == "Data" THEN .... /// Note the MIN(), which we need since the table calc is an aggregate (and, therefore all of the parts of the IF need to be aggregates).
2. Create a new calc Ads with total that returns the above value if we're in the "data" rows and returns a WINDOW_SUM() if we're in the "total" row.
IF MIN([Data Source]) == "Data" THEN [Ads]
Replace the current Ads pill with this new pill. Everything should look the same for the data rows, but now you should have a value in the total row. Most likely it will be incorrect, since the compute using is not setup yet.
3. Adjust the compute using.
Click on the Ads with total pill > Edit table calculation. This is a nested table calc and you'll therefore see a pull-down menu with both Ads and Ads with total. First select Ads and select Advanced in the compute using. This should open a dialog box with partitioning and addressing sub-boxes.
There are probably a couple of ways to set the table calcs, but I moved everything over the addressing side. The order is important, since the addressing is hierarchical. In this view the hierarchy is test, brand, cnt header, data source. So put test on top and data source on the bottom. Click OK and back in the compute using box, select "restart every" brand.
Repeat this for the Ads with total---same addressing order, same restart every "brand".
Now the totals should be correct.
The last step is to show / hide the cnt rows. To do this, I used the table calc filter technique mentioned above. I created the field cnt table calc filter=
IF MIN([Data Source]) == "Data"
You can read more about why this works in the Tableau link above. But basically the table calc filter is just removing information from the visual layer--the data is still available in the view for the calculations.
Add this to the filter shelf, edit the compute using to use the same addressing as above (test > brand > cnt header > data source), but don't restart the partitioning.
Make it a quick filter and how you can add / remove rows. Including all of the rows.
(I hope this is actually what you wanted )
A couple of final points:
- The downside of using the Custom SQL approach to totals is that you need to remember your data source now has duplicate rows. If you create a new, unrelated worksheet make sure to remember to add a filter for data source = "data".
- I duplicated the data source to show option 2. You don't need to do this---I'm not blending--but using a second data source can help with the previous issue and I wanted to keep your original data intact.
Sample2_jimw.twbx.zip 30.8 KB
Thanks Jim for the detailed explanation.
It is really awesome to get such detailed advice on how to tackle such queries in tableau.
Also I would like to know how to display only totals and not the column values as per your explanation. I would also not use the query like you have mentioned. I have the data and only need a calculated field to display values in totals only for a column.
I don't quite understand your target view, but if you just want to show the totals like this
Then you don't need anything fancy (dual data source, ...). A simple table WINDOW_SUM on Ads works:
IF FIRST() == 0 THEN
Replace Ads with Ads Total in the view.
You'll have to put cnt on the level of detail shelf as a discrete dimension (blue pill with no aggregation). And set the compute using to customer with addressing test > brand > cnt, resetting every brand.
See attached workbook sheet called simplified. ...
Sample2_jimw.twbx.zip 46.4 KB
OK, now I better understand your goal.
You have two options, depending on whether you want the user to hide/show the cnt rows.
If you do not need this functionality, then Option 1 might be simplest.
This solution is based on the fact that you can determine when you're on the subtotal row by checking if the min(cnt) == max(cnt).
What complicates your case is that you want to subtotal a table calculation, but when Tableau calculates the subtotal, it removes the right-most dimension (cnt), which causes problems for your table calc Ads.
The trick to avoid this problem is to add a duplicate of cnt on the level of detail shelf, so that when tableau removes cnt, the duplicate is still there. To create the option 1 tab:
1. Right-click on cnt and selected duplicated. This creates cnt (copy)
2. Add this field to the level of detail shelf.
Now in the subtotal row, you'll see a total for each cnt (copy).
To hide subtotals for search and Imp, wrap the min(cnt) / max(cnt) in a WINDOW_MIN / MAX function and set compute using to cnt (copy).
Imp (blank subtotal)
IF WINDOW_MIN(min(cnt)) == WINDOW_MAX(max(cnt)) THEN sum([Imp]) // normal value for non-subtotal row ELSE NULL // blank for subtotal END
3. Ads Total
Since you only want to show the subtotal value for this column, add a if WINDOW_MIN(min(cnt)) != WINDOW(max(cnt)) to the calculation, which will be true when you're on the sub total row. Add this to the view and select compute using cnt (copy).
4. Now your view should look good, except for the white space. To remove this, select Analysis from the top menu bar, then Stack Marks > Off.
If you want to hide/show cnt rows, then you can modify the Option 2 from the previous post, you can just modify the Ads with Total field to return the WINDOW_SUM() value when data source = "Total" and return null values when the data source is "Data".
Sample2_jimw_v2.twbx.zip 33.4 KB
Thanks a lot Jim...A silly question....what if I do not have the option to create "Data Source" field in the table.
I am trying to extract data from database rather than the excel.
The data source field is added in the Customer SQL option within Tableau---see the first code segment in my first reply above---and, therefore, you should be able to do this with any data connection.
When using this technique, you need to remember that there are twice as many rows---SUM(Sales) will now be twice as large---unless you filter out the Total data source or create calculated fields for measures that have an IF [data source] == "Data".
To avoid problems, you might want to just use this data connection for sheets where you need the custom subtotal functionality.