4 Replies Latest reply on Sep 12, 2012 4:41 AM by thierryjakircevic

# Problem using Table Calc as a Global filter

Hi,

In order to give our users the ability to hide/show dimensions without affecting the percentages (Quick Table Calculation --> Percent of Total), I am using a table calculation such as LOOKUP(ATTR([NameOfMyDimension]),0) and created a quickfilter from this.

This works great but I need to use this quickfilter as a Global Filter. I have attached a sample demo of the principle (the actual workbook contains over 100 brands, hence the importance of Global Filter to avoid duplicating the quickfilter for all views).

Implementing it in my dashboards would be a very powerful feature. Hope someone can work out some magic here!

Thierry

• ###### 1. Re: Problem using Table Calc as a Global filter

Hi Thierry,

As far as I understand them, global filters are only for filters that work in the data source, whereas table calculation filters are computed within the view in Tableau. Since table calculations are completely dependent on the fields in the view, it would be extremely difficult to guarantee that table calculation filters would reliably work across different views.

I believe your best option in this situation is to use a parameter-based filter, which has the downside that any time a new brand is added you would have to adjust the parameter list.

Jonathan

• ###### 2. Re: Problem using Table Calc as a Global filter

Hi Jonathan,

Are you sure about calculated fields not being able to be used as Global FIlters? In some instances, it appears that I can use a calculated field as a Global Filter. Does it depend on the type of formula used? If so, may be there is a way to make the formula I am currenly using "Global FIlter compliant"?

With parameter based filter, can I still maintain the desired behaviour (ie: applying filter does not recalculate the percentages but is instead similiar to "hiding" dimensions from the view)?

Also the intendend use of the dashboard is to allow users to make side by side comparison of different brands of products. I would need to let them select multiple brands but I think parameters do not handle multi-value selection are they?

Thierry

• ###### 3. Re: Problem using Table Calc as a Global filter

Hi Thierry,

I apologize for the confusion, let me try to clarify: Calculated fields that are based on table calculations (i.e. any pill that has a triangle icon) cannot be used as Global Filters, for the reasons I described. As far as I know, all other calculated fields can be used as Global Filters - I haven't found any exceptions.

Yes, you can have versions of your existing calcs using the % of total with a parameter-based filter. However, as you noted, parameters are single select and not multi-select. I can think of a couple of (non-ideal) workarounds:

If users are just comparing two brands, have two parameters that each have the list of brands and then set up the filter to look at both, the calc would be something like LOOKUP(ATTR([Brand]),0) == [Parameter 1] OR LOOKUP(ATTR([Brand],0) == [Parameter 2], and then you'd filter for just True results.

Generate the total that is used within the % of total  within the underlying query or via Custom SQL so that it is an additional column available to every row. Then you don't need a table calculation to do the % of total and can use Tableau's regular filters - for example, for your dashboard needs you could use an action filter on Brand. Obviously, this might not be workable if users are able to dynamically adjust other elements of the view such as changing a date range, but in some circumstances it is feasible.

I saw you posted on http://community.tableau.com/ideas/1313 about multi-value parameters, here are a couple of other parameter-related ideas http://community.tableau.com/ideas/1178 and http://community.tableau.com/ideas/1521.

Cheers,

Jonathan

• ###### 4. Re: Problem using Table Calc as a Global filter

Thank you again Jonathan. Really appreciate the help.

I had voted for dynamic parameters (wish I could cast 10 votes!) a while back but was not aware of Joe's idea (rethinking the UI of the Idea section might be needed to help improve the visibility of ideas with low/mid vote count).

The workaround using two parameters is very clever! Since I only need 2 or 3 brands in the comparative analysis, it does not negatively affect my screen real estate.

As for the second workaround, my brain needs a good night sleep to understand it. Too late here to work on it now

Your comment would fit very well in the new blog post created today by Emiliano (in which I also cried for help )

Thanks again!!

Thierry