1 2 Previous Next 24 Replies Latest reply on Sep 24, 2016 10:30 AM by Jonathan Drummey Go to original post
• ###### 15. Re: How to filter and choose a part of data?

Hi Irina ,

You can refer to LOD Expressions to understand it better.

Yes you can choose Product group by box with some limitation. I will mention a way which i know.

1)You can create a parameter which contains the values of Product group . use this parameter in calculation.

But the limitation is parameter will be static , and you can not select multiple values from parameter. To select multiple values in parameter , if you are having less number of values in product group you can create combinations in parameter values list like Fruits&Drinks like this , and use string function like contains in calculation.

• ###### 16. Re: How to filter and choose a part of data?

This Conditional Filter could even be done as a computed Set.

A computed set would possibly be the best option if its members could be changed by dashboard users with multiple-values filters and therefore hope following ideas get more up-votes:

Multiple-values filters are available, but seemingly only if the set is used as a filter, which doesn't solve this challenge because it also filters out the other product groups:

• ###### 17. Re: How to filter and choose a part of data?

Attached workbook has now a separate sheet for each of these methods:

• Self-blend
• LoD
• Table Calculation
• Set

The only method that has multiple-values filter is the self-blend.

The other 3 methods can either use a single-value or free text parameter filter.

The latter is used in attached workbook.

It would be interesting to know which of these ( LoD, Table Calculation, Set ) perform best.

Here is a screenshot with the Set method:

Attached Workbook Version:  9.0

1 of 1 people found this helpful
• ###### 18. Re: How to filter and choose a part of data?

SUM(IF [Product Group] = 'Fruits' OR [Product Group] = 'Drinks' THEN 1 ELSE 0 END) > 0.

Now I figured it out. So basic    ... just choose the field one wants to "group by".

I wonder if this method also can be used if the need is to "group by" two or more fields ?!

I know, "group by" isn't a good term. It would be nice to know how Tableau actually executes this filter.

Sadly, only the self-blend method has multiple-values filters.

The other methods can use a single-value or free text parameter control:

1. Aggregation
2. Set
3. LoD
4. Table Calculation

I listed these in the order I think they perform best. That said, I am just guessing.

Here is a screenshot with the Aggregation method:

Attached Workbook Version:  9.0

1 of 1 people found this helpful
• ###### 19. Re: How to filter and choose a part of data?

Here's what I was talking about with a conditional filter and a computed Set that uses a conditional. In the "conditional set jtd" worksheet I used a conditional filter on Number of Order that uses the inner portion of the formula you'd created for the Filter PG (LOD) calc:

Then I right-clicked that filter and chose "Create set...". I could have done the set creation directly from the Number of Order field as well in the dimensions window.

As far as adding or removing members from the conditional filter or set at runtime the parameter solution that you proposed is probably the easiest one.

Jonathan

1 of 1 people found this helpful
• ###### 20. Re: How to filter and choose a part of data?

I have checked all the methods you proposed. Best of all I like Aggregation and LOD. I have done the analysis on real data and I used LOD because I had to include 2 groups and exclude 4 groups and I used two filters. The idea of duplicated bases is perfect. I will use it for sure in another situation. Thanks a lot.

• ###### 21. Re: How to filter and choose a part of data?

FYI the conditional filter (what you'd called "aggregation" here), computed Set, and LOD expression would essentially all have the same performance since they all work by creating a subquery that is joined in to the main query. So the query is something like:

SELECT [Order Number], [Product Group], etc.

FROM data AS 1

GROUP BY [Order Number]

INNER JOIN (SELECT [Order Number] FROM data HAVING SUM(IF [Product Group] = 'Fruits' OR [Product Group] = 'Meat' THEN 1 ELSE 0 END) > 0) AS 2 ON 1.[Order Number] = 2.[Order Number]

When I did experiments when v9.0 came out with LODs the queries were *exactly* the same for conditional filters & computed Sets in simple cases like this.

Jonathan

1 of 1 people found this helpful
• ###### 22. Re: How to filter and choose a part of data?

Pure gold! Thank you!

With this understanding, my general favorite will be LoD, because it is easiest to read, understand, write and share. In addition, it can easily "group by" more than one field when needed. I couldn't figure out how to do that for conditional filters and sets and wonder if it is even possible.

Data blending and table calculations are different animals that I generally avoid for data purposes such as filtering and dynamic reshaping, but in this question's scenario, data blending is the only method which has multiple-values filters and therefore would be my best choice if performance is acceptable.

An annoyance with secondary source filters is that they have a null option that confuses.

This KB article and this active idea (54) convince me that it can't be removed or hided.

A good thing to say about this null is that it will remind people to switch to a better method when such becomes available.

-oOo-

Understanding what is going on makes it much easier to think how to do what, and now I do understand, because I got an explanation in my native data language (SQL). With this in mind, I like to share a COUNTD filter which I believe Irina also would like to have, so she is not limited to OR but can include AND and everything in between:

{ FIXED [Number of order] : COUNTD(IF CONTAINS([Parameter PG (All)],[Product group]) THEN [Product group] END) }

Sadly, I couldn't figure out how to use COUNTD on product group from blended source. To make something work with the multiple-values filter from the secondary source, this formula was hard-coded for each product group:

MAX(IF [table (copy)].[Product group] = 'Fruits'     THEN 1 ELSE 0 END) +

MAX(IF [table (copy)].[Product group] = 'Vegetables' THEN 1 ELSE 0 END) +

MAX(IF [table (copy)].[Product group] = 'Meat'       THEN 1 ELSE 0 END) +

MAX(IF [table (copy)].[Product group] = 'Drinks'     THEN 1 ELSE 0 END)

Attached Workbook Version:  9.0

• ###### 23. Re: How to filter and choose a part of data?

Hi all,

There would be another way of getting the same result --

a properly filtered [Number of order] values on a Target view --

starting from [Product Group] Quick Filter on a Source view.

Sure you know what it means -- a Filter Action, of course ;-)

But this would require a necessary second step

(besides of choosing values from a quick filter),

namely, selecting all Marks at once on a Source view.

I've borrowed a quick fix from this thread:

Is there a way to have a "Select all" button for a list?

Yours,

Yuri

PS Below is the link to the same dash on Tableau Public --

to test the "Show / Hide" behaviour over the web:

Tableau Public

Sure there would be JS API function(s) to achieve the same.

• ###### 24. Re: How to filter and choose a part of data?

Ooh, thanks for reminding me of that technique, Yuriy!

Here’s how to set this up:

1) Build the original worksheet, I call this the “target” worksheet.

2) Build a worksheet that has a dummy header (the Show/Hide in Yuriy’s sheet) as a discrete pill on Rows or Columns and the dimension(s) that you want to filter on (Number of Order in this case) on the Level of Detail shelf. Set up the view to hide all the marks so the only thing the user can do is click the header. I call this the “filter button” worksheet. Yuriy used a Gantt bar mark with 0% transparency, my current favorite is to use a Polygon mark, it’s even more invisible than a Gantt bar mark.

3) Add a filter to the filter button worksheet for the dimension you want to filter, that’s Product Category in this case.

4) Build the dashboard, adding the target worksheet, the filter button worksheet, and the filter from step 3.

5) Create an action that uses the filter button worksheet as the origin and the target worksheet on the target that uses the dimension(s) you want to filter on, and most important set it to “Exclude all values”.

The user interface flow is:

1) Pick the quick filter item(s). This invisibly updates the filter button worksheet.

2) What happens next depends on whether the target worksheet is visible or now:

a. If data is not displayed in the target worksheet (the “Hide” situation”) then the user can click the Show/Hide button once and the target worksheet will display with the filter.

b. If the data is displayed in the target worksheet (which is also the case when Show/Hide has been selected) then the user needs to click the Show/Hide button twice. Once to unset the Show/Hide button, and once more to apply the Filter Action.

When I’ve used this in the past there have been a couple of issues:

- The user experience of having to click once or twice is obviously not ideal. It gets especially challenging if the dashboard is such that re-rendering the view takes awhile (“awhile” being dependent on the definition of the interactor).

- Performance can be an issue when there are many values being passed in the Filter Action. For example if I’m filtering from a database of orders for the selected product combinations then however many order IDs meet the criteria will be pulled into the filter worksheet and passed via the Filter Action to the target sheet(s), if there are thousands or more order IDs selected that can end up being prohibitively slow.

This all reminds me of one more technique I’ve used for this in cases when I’ve got more ETL capabilities available (like creating database views or tools like Alteryx) and that is to:

1) Pre-aggregate the data if possible and/or necessary to reduce the number of records.

2) Create a cross product of every combination of the "market basket" dimension(s) (in this case Product Category) with the original data.

3) Build Tableau views where the product is on the added market basket dimension(s) and then calculated fields to deal with the finer grain of the data.

Jonathan

1 of 1 people found this helpful
1 2 Previous Next