3 Replies Latest reply on Jan 17, 2019 1:33 PM by Jim Dehner

    Filter from primary source on one sheet to secondary on another sheet

    Patrick Kriebel

      Hey Y'all,

       

      I don't think this is possible, but I want to post to the forum as a last ditch effort.

       

      I have two data sources. One source is spend data by DMA (Designated Marketing Area) for different retailers. The second source is a list of zip codes and the DMA they belong to (DMA to zip code is a one-to-many relationship, each DMA contains many zip codes).

       

      I have two worksheets. One worksheet (sheet A) has some summary statistics about spend in the DMAs over time, and a multi-select filter for retailer. The user can select a set of retailers, and the table displays relevant metrics. On this worksheet, the DMA spend data is the primary (and only) data source.

       

      The second worksheet is a map (sheet B). The geographic data source is the primary source, and the DMA spend data is the secondary source.

       

      I need to apply the multi-select filter from sheet A to the spend data on sheet B.

       

      The filtering update in version 10 which allows filtering secondary data sources is irrelevant here, and does not help with this problem at all. Neither does the use of a parameter because parameters lack multi-select.

       

      The workbook must have the option to apply a filter one sheet A to the spend data (primary source on sheet A) and have that same filter apply to the same exact data source on sheet B (where it is the secondary source).

       

      The two data sources cannot be joined either inside of Tableau or outside of Tableau because of the one-to-many relationship. The DMA spend data has spend for a given (DMA, Retailer, Month, Year), and the geographic data is simply a list of (Zip code, DMA). So retailer only exists in the spend data.

       

      I cannot share the workbook as all of this data is proprietary.

        • 1. Re: Filter from primary source on one sheet to secondary on another sheet
          Jim Dehner

          Hi Patrick

           

          Interesting problem -

          Just for a second let's back away from the 2 data source issue (you may be right that you can't connect the 2 but I have some questions first)

           

          If I understand the problem you have some business metric data that is aggregated (outside tableau) at the DMA level - that data does has dimensions for the retailer, and dates but at the retailer/date level there is one value for the DMA?

           

          at the same time you have some geographic data that ties DMA to the Zip code  -

           

          what is not clear to me is what are you trying to do with the business metrics?   - what do you want to show the end user - spend /retailer/date? within the DMA  or was there some other goal?

           

          Jim

          • 2. Re: Filter from primary source on one sheet to secondary on another sheet
            Patrick Kriebel

            Hey Jim,

             

            See below for answers to your questions.

             

            If I understand the problem you have some business metric data that is aggregated (outside Tableau) at the DMA level - that data does has dimensions for the retailer, and dates but at the retailer/date level there is one value for the DMA?

             

            Correct. Our metric data consists of a file with the following columns: DMA, Retailer, Month, Year, Spend. So each row has the total spend amount of a specific DMA, by month and year.

             

            at the same time you have some geographic data that ties DMA to the Zip code -

             

            Correct. We have a file with two columns: zip code, DMA. Each DMA consists of multiple zip codes.

             

            what is not clear to me is what are you trying to do with the business metrics? - what do you want to show the end user - spend / retailer / date? within the DMA or was there some other goal?

             

            ultimately we want to produce a dashboard with two panels:

            • one panel has a map of the DMAs, and they are color-coded by a metric (let's just say total spend for a selected month)
              • geocoded DMA file is the primary source for this worksheet (in order to build the map from the DMA, Zip code hierarchy)
              • the DMA spend data is the secondary source (in order to pull in spend to color code the map)
              • the linking field is DMA
            • the second panel has a table of summary statistics: spend growth (calculated comparing the selected month with the same month from the previous year) and some other values.
              • this is just a data table
              • DMA spend is the primary data source for this worksheet

             

            Currently, for the data table, the user can click a drop down menu and select the retailers they're interested. This filters the records to show only spend data for the selected retailers.

             

            I want to apply this filter to the spend data on the map worksheet. If a user filters the data table data to show summary stats for just retailer A, retailer B, and retailer C, then spend total that color codes the map should use the same filter.

             

            Tableau, however, appears unable to do this. I can use the "apply filter to selected worksheets" option from the summary table worksheet and apply to other worksheets where the spend data is the primary source. I can also set a retailer filter on the map worksheet and apply it to other worksheets where spend data is the secondary source. But I cannot get the filter from one to the other.

            • 3. Re: Filter from primary source on one sheet to secondary on another sheet
              Jim Dehner

              OK

              I understand - if you can pull together sum dummy data in an excel sheet file

              2 sheets - one with some dummy business metrics data (the file A) - include enough that there are a couple of retailers and dmas

              and the second sheet with the geo data with the same dmas as file A

              you can post it here or send it to me at Marketanalyticsllc@gmail.com if you are concerned about confidentiality

              this can be done -

              Jim