12 Replies Latest reply on Mar 13, 2017 1:55 PM by mark.welsh

    Filter Action with background data - not on viz

    mark.welsh

      I have a bar graph that shows sum of sales for the last 7 days.  The only field used by the viz is Sales.  Can I use the bar as a filter on a dashboard to filter a second sheet by only the Customer Names that are in the background data of the bar?  Since Customer Name isn't on the viz, I get a missing fields error in the filter action, but since the Customer Names are behind the scenes of the bar, how can I use those as a filter for the second sheet?

       

      Superstore example attached.

        • 1. Re: Filter Action with background data - not on viz
          Karthik Venkatachalam

          When you are thinking about filters in a conservative sense, you can only filter by "Dimensions" and not "Measures". Your Sales is Measure, and you can click on it all day, and it wont do a thing. Because the underlying records from 7 days, make up those Summary bar you are seeing. (be it zero/ non-zero). Since we know underlying records are filtered by "Ship Dates", only thing you can do is to apply that filter to all sheets/datasource, so, your workbook sheets are showing at the same level.

           

          However, if you have any dimension added to the Bar graph, then at that point, your click action filters would come into play, and try to apply the same dimension to other sheets.

           

           

          Once you apply, "Date" filter to all sheets, you will see customers filtered at the same level as the summary sheet.

           

          • 2. Re: Filter Action with background data - not on viz
            mark.welsh

            Thanks Karthik.  So, in order to use a sheet dimension as a filter, the dimension must be present in the sheet in which to filter from, correct?  I was hopeful there was some trick to filter using the background data that builds the bar graph - if that makes sense. 

             

            The use case is that I don't want to add Customer Name to the bar, because I only want to show a total for the last 7 days.  The table below will be filtered by whatever dates the user chooses.  Then, the user needs the ability to only show the customers who have come in the last 7 days in the table below, but it needs to show all of those customers ship dates, so I can't filter by ship date I can only filter by customer name.  I was thinking the easiest way to achieve this is to click the bar and filter the below table by the customers that make up the bar.  I don't believe this is possible, however.

             

            Looks like I'll need to create some calculations that find customers with ship dates in the last 7 days and use that as a filter.

            • 3. Re: Filter Action with background data - not on viz
              Karthik Venkatachalam

              I see. If I understand you right, what you are asking is easy to do.

              - You just need to add Customer Name to the filter in the bar sheet and make sure you "Apply the filter to datasource /all sheets"

               

              - Now go back to dashboard, and add the Customer Name filter and make sure you pick relevant values. that way, your Customers from 7 days, are the only ones that will show up.

               

               

               

              Finally it should look like this.

               

               

               

               

              Attaching the workbook.

              • 4. Re: Filter Action with background data - not on viz
                mark.welsh

                Thanks! 

                This is definitely on the right track, but I may be looking at it wrong.  The user needs to be able to change Ship Date on the fly to filter the Bar graph which would then filter the table by the new customers.  Is this possible with this method?

                • 5. Re: Filter Action with background data - not on viz
                  Karthik Venkatachalam

                  Yes, you just need to show the date filter, and you are done. You can change the dates....and customers would change accordingly., and so would your measures bar. I am just going to ignore the word "New Customers", you typed in here, because you haven't mentioned any about it before.  Its straightforward, and I don't see a reason for struggle in this. Why don't you add the date filter and let us know if you are seeing what you expect?

                  • 6. Re: Filter Action with background data - not on viz
                    mark.welsh

                    "New Customers" = customers that were added to the view as a result of the new Ship Date filter.

                    I've added Ship Date filter for bar graph which works as expected, but there is no cascading filter action for the Table.  How can we make Ship Date filter the bar and then pass the "new" list of filtered customers to the Table?

                    • 7. Re: Filter Action with background data - not on viz
                      Karthik Venkatachalam

                      Are you sure you "Applied" the filters to the Table as well?

                      If you did, then it should show the "new customers" as well.

                       

                      • 8. Re: Filter Action with background data - not on viz
                        mark.welsh

                        I cannot apply Ship Date filter to the Table, because it will limit to those Ship Dates only.  The goal is to limit the Bar by Ship Date then show ALL Ship Dates on Table for the filtered set of customers on the Bar. 

                         

                        Filter Bar by Ship Date, take that list of customers and filter Table.

                        • 9. Re: Filter Action with background data - not on viz
                          Karthik Venkatachalam

                          Per my understanding, the filters don't traverse/link many levels like that, unless enforced/linked by another Dashboard or Filter action.

                          Having said, this is the closest I could get to what you asked.

                          1) Apply "Ship Date" filter to Bar graph sheet only.

                          2) "Customer Name" filter would be from Bar graph sheet, which applies to the bottom sheet.

                           

                          If you change the dates, you would see the Customer name filter change. (You see 2 customers for 01/05/2017).

                          But this would not automatically control the bottom sheet. On the first load, bottom sheet would initially display all records. Of those, if you want to view all records for Ben/ Chuck, you would have to select the individual customers. But, this may be confusing to the users. Suggest not to do this way. May be someone else could help you out here.

                           

                          • 10. Re: Filter Action with background data - not on viz
                            mark.welsh

                            Thanks again Karthik.  That's the behavior I was seeing as well.  It would be nice if Tableau could pass background data fields from one view as filters to another view.

                             

                            I think I can come up with a few ways to make it meet the user's needs without the original functionality I was hoping for.

                             

                            Appreciate the help!

                            • 11. Re: Filter Action with background data - not on viz
                              Ivan Young

                              Hi Guys,

                              There are some tricks to using action summary source sheets to filter more detailed target sheets without adding unwanted detail to the summary.

                               

                              The answer to the original question is yes you can do this by creating single value dimension which will lookup the customer records.  I'm a little confused on the following 'The user needs to be able to change Ship Date on the fly to filter the Bar graph which would then filter the table by the new customers.'  I though the Bar Graph was supposed to show the last 7 days or 7 days from the Max Date.

                               

                              The way you would be able to solve the question in the OP is to create a dimension: IF DATEDIFF('day', [Ship Date],{ FIXED : MAX([Ship Date]) }) < 7 THEN 'Show' Else 'Hide' END which you would use as a filter in your bar chart and also add to detail.  You would also add this field to  detail of your target list of customers so you would have a dimension to link your source and target.  Not sure if this is what you really wanted to accomplish.  Let me know if you have any questions.

                               

                              Regards,

                              Ivan

                               

                              • 12. Re: Filter Action with background data - not on viz
                                mark.welsh

                                Thanks Ivan.  Yeah, the request changed after I said 7 days, so now it needs to be a user selectable data range. 

                                I'll play around with your suggestion.  So, it doesn't filter the Table by Ship Date, it filters the Table by the Customers from the Bar view?