12 Replies Latest reply on Feb 14, 2013 12:14 PM by Don Thompson

    Filters not applied to Secondary Data Sources when *non-filtered* fields are shared

    Don Thompson

      Hi, this is a strange issue to describe but here goes...

       

      When I'm blending data in a view from multiple sources, and I add a dimension to the Filters shelf for a field that exists on all three sources, e.g., company division #, then the filter only seems to "take" on data coming in from the Primary Data Source for certain records. Data from Secondary Sources is not being excluded by the filter in a very specific way, i.e., when certain data records have a field value in common with non-filtered records (a field they're not being filtered for) then the filter isn't applied to the secondary data. For example, I have lists of Purchase Order records that are tagged with (1) a division and (2) the name of the vendor. A vendor may be used by a single division, or multiple divisions. If I filter to exclude POs for one division, then POs in that division for a vendor that's shared by the other divisions (not filtered out) will still be included in the view when that data is coming in from a Secondary Data Source.

       

      In my sample files, the Primary Data Source is "SAMPLE COST DATA," secondary files are SAMPLE SHIPPING DATA and SAMPLE CLAIMS DATA. The filter for the field, DIVISION, was pulled onto the filter shelf from the Primary Data Source. Data from purchase orders for vendors used only by the excluded division(s) are filtered out from both Primary and Secondary Data Sources. But data from vendors AWESOME APPAREL and US COMPANY (which are shared across three divisions), are not filtered out from the Secondary Data Sources.

       

      See sample Tableau file tab  "FILTERED OUT DIVISION 150" and tab "FILTERED OUT ALL BUT DIVISION 10." The columns for LY UNITS, TY UNITS, LY COST, TY COST are accurate since they come in from the Primary Data Source.  But columns LY SHIPPED UNITS, TY SHIPPED UNITS, LY CLAIMS UNITS, TY CLAIMS UNITS (from secondary sources) are inflated by data for AWESOME APPAREL and US COMPANY because they include data from the division(s) that should filtered out.  The pivot tables on each Excel file show the values that should "really" be in the Tableau view.

       

       

       

       

       

       

       

       

       

      If this isn't totally confusing, can anyone suggest how I can make the filter apply to *all* the data from the secondary sources?

      Thanks for any assistance, this is driving me crazy!

      - Don

        • 1. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
          Joe Mako

          What you are looking for is being add to Tableau for version 8, and is just a click away.

           

          You can request access to the beta from your Tableau representative.

          • 2. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
            Don Thompson

            Hi Joe,

             

            It's nice to know the Beta can resolve, but in the meantime is there any kind of workaround available?  Our IT group is lean and busy and I can't speak to their ability to starting installing Beta's on our desktops.

             

            I saw an article on getting data from secondary sources in the knowledge base in this link, but it's thinly discussed and I'm not sure if there's something I can take away from this as a workaround.

             

            http://kb.tableausoftware.com/articles/knowledgebase/creating-groups-using-calculated-fields

             

            For now, I tried putting a conditional statement into the formula for data from the secondary sources to exclude a filtered out division, like this:

             

            Name:  TY SHIPPED

             

            Formula:

                 IF [DIVISION] <> "150" THEN

                      IF [YEAR] = "2013" THEN

                           [UNITS SHIPPED]

                      END

                 END

             

            The problem is that I have *six* divisions, and if I want to make a version of this report for each division, I'd have to have six versions of every formulas for every column from the secondary data sources in the report.  There are 36, so that's 216 versions of the same formula to specify data that should really be addressed by the "main" filter.

             

            Is there anything I can derive from the link above, or other kb link, to help? Anything else you can suggest?

             

            Thanks for taking the time, we really appreciate it,

            - Don

            • 3. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
              Joe Mako

              There are a few other options, including using a scaffold data source, this is great when your desired level of detail is static, or you are okay with Cartesian joins.

               

              The route I would use is table calculations. They enable you to blend at one level, and then aggregation to another higher level. So you can blend at the agent-source group-vend name-division level, and then aggregate to the agent-source group-vend name level.

               

              This requires an additional calc field for each column you want, as done in the attached.

               

              example formula:

               

              IF FIRST()==0 THEN

              WINDOW_SUM(SUM([LY COST]),0,IF FIRST()==0 THEN LAST() ELSE 0 END)

              END

               

              with compute using on division.

               

              Also it is good to have a separate calc field to bring data across a data blend.

              • 4. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                Don Thompson

                Scaffold data, cartesian joins, and the formulas you gave are all new concepts for me so I'll have to review to understand.

                 

                However, I used the Division filter tool you added and checked the numbers and they're correct so this seems like the way to go for now.  I'll respond back if it's not coming together for us.

                 

                Thank you very much!

                • 5. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                  Don Thompson

                  I'm afraid I haven't been able to re-create what you did on my original file.

                   

                  I can create a calculated field that points to the data source I'm blending in, e.g.,

                   

                       TY SHIPPED UNITS as SUM([SAMPLE SHIPPING DATA (SAMPLE SHIPPING DATA.xlsx)].[TY SHIPPED UNITS])

                   

                  and I tried to create a second calculated field as a Table Calculation like yours but it's "configuration" is different.  Yours says "Compute using DIVISION" but the DIVISION field is not available for me to pick when I try to create.

                   

                  Is is possible for you to list the actual steps you took to setup these fields? There must be something I'm leaving out because I'm not sufficiently familiar with the multi-level data aggregation you mentioned. If I know your concrete steps, then I can recreate in my sample file and our actual data files.

                   

                  Then I can figure out what it all means later...   :-)

                   

                  Your assistance is very much appreciated!

                  • 6. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                    Shawn Wallwork

                    Don do you have DIVISION on the level of detail shelf like Joe does? [The one from the primary data source.]

                    1 of 1 people found this helpful
                    • 7. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                      Don Thompson

                      Shawn, I did not have it there, but as soon as I moved it there, the numbers corrected!

                       

                      If you have an extra minute, could you explain what the significance of that is?  I went back to the Help discussion of Addressing and Partitioning for table calculations and I didn't see anything about this.  Is there an article on this topic that you could recommend for new Tableau users?

                       

                      Thank you both!

                      • 8. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                        Don Thompson

                        Mmmmm, now I've got "overlapping text."  Somehow on Joe's version, if multiple Divisions are included in the filter, there are single values in the table. But when I do it, the totals show overlapping values.

                         

                        Strike the above, when I use a version of the calculation with the Table Calculation Functions (like Joe's), then those columns *don't* have overlapping text.

                        • 9. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                          Shawn Wallwork

                          Don I don't have an article handy, but essentially the fields that are available for use in table calculations are those fields that are "in the view". [This in the view requirement will change in the next version of Tableau due out next month.] So to get fields in the view without "using" them in the visualization, we commonly put them on the level of detail shelf. This doesn't always work, because many times putting a field on this shelf can change the viz. The only other thing you should be aware of is that fields on the filter shelf are not treated as "in the view".

                           

                          But like I said, this all changes next month.

                           

                          --Shawn

                          1 of 1 people found this helpful
                          • 10. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                            Shawn Wallwork

                            Don did you do this form of calculation for all those table calcs:

                             

                            IF FIRST()==0 THEN

                            WINDOW_SUM(SUM([LY UNITS]),0,IF FIRST()==0 THEN LAST() ELSE 0 END)

                            END

                             

                            If not that's where your problem is.

                             

                            --Shawn

                             

                            EDIT: By the way, as Richard just mentioned this construction will not be needed in V8. The new form will be:

                             

                            IF FIRST()==0 THEN

                            WINDOW_SUM(SUM([LY UNITS]))

                            END

                             

                            Which is a heck of a lot easier to read. If your data set is small, you should probably use it now, then you won't need to change it out later.

                             

                            Message was edited by: Shawn Wallwork

                            1 of 1 people found this helpful
                            • 11. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                              Joe Mako

                              Don,

                               

                              You are welcome to get in touch with me, and I would be glad to help any way I can.

                               

                              If you are looking for a collection of resources, see http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/ for some good links.

                              1 of 1 people found this helpful
                              • 12. Re: Filters not applied to Secondary Data Sources when *non-filtered* fields are shared
                                Don Thompson

                                Hi Joe and Shawn,

                                 

                                Thanks again for all your help!  Took me a few days to update all our formulas using the method you described but in the end it worked out.

                                 

                                As a followup to anyone else who comes across this posting, I had another set of formulas for reporting the Count and Count Distinct on some Dimensions in my Secondary Data Source.  In order to do that, I had to adapt the method above as follows:

                                 

                                In Secondary Source:

                                  Dimension name:  PO-NUMBER

                                  Copied into Measure box as:  PO-NUMBER (copy), set Field Properties, Aggregation, as Count Distinct

                                 

                                  Created Calculated Field:

                                  Name:  TY # PO's

                                  Formula:     IF [YEAR] = "2013 YTD" THEN

                                                         [PO-NUMBER (copy)]

                                                    END

                                On Primary Source:

                                  Created Calculated Field:

                                  Name:  TY # PO's (blended)

                                  Formula:  COUNTD([DATA_POSHIP_2013.01.26].[TY # PO's])

                                 

                                  Created Table Calculation:

                                  Name:  _TY # PO's

                                  Formula:     IF FIRST()==0 THEN

                                                         WINDOW_SUM([TY # PO's (blended)],0,IF FIRST()==0 THEN LAST() ELSE 0 END)

                                                    END

                                     Set table calculation to calculate along the Division field in the Level of Detail box.

                                 

                                Even though I was looking for a distinct count of unique PO numbers, I still had to ultimately use the WINDOW_SUM formula.  Took me a while to figure out, because it seems counter-intuitive to use WINDOW_SUM when there's a WINDOW_COUNT formula, but I'm still a beginner at this...

                                 

                                Hope this helps others.

                                - Don