3 Replies Latest reply on Mar 17, 2017 1:00 AM by Abhijeet Bijoor

    Blended Data Problem

    Lee Forst

      Attached is an example of what I'm trying to do and the problem I'm having.  I'm connecting to two different sheets of the same Excel file; Employee and Invoices.  On the Employees sheet, I'm showing the number of employees by location.  The Invoices sheet shows the number of invoices and Amount by location.  The Blend sheet is where I'm blending the two data sources together. In this sheet, I'm showing by location, the invoice count, amount, employee count, avg invoices per employee, and avg $ per employee.  I also have a filter on Location.  Everything is fine.  I can filter by the Location and see just that row.  However, the goal is to put this information on a dashboard and the user will only see one row if they select (All) locations.  Or if the user selects a specific location, the information shown would be for the store.  I thought I would just remove the Location off the rows shelf and life would be good.  However, if I do that then the blend breaks.  I've tried everything I can think of to get this to work.  Do you have any recommendations?  Somehow I have to get Locations into play with out affecting the level of detail displayed in the view.

       

      I'm using version 9.3.x

        • 1. Re: Blended Data Problem
          David Li

          Hi Lee! The reason your blend breaks is that you're blending with Location as a linking field, so when you remove that from your view, Tableau can't blend any longer. In a blend, the secondary data source can only contribute aggregated values using the level of detail defined by the linking fields that appear in the view, so when you remove them, the blending calculation essentially becomes undefined.

           

          Would you entertain the idea of giving users a parameter that lets them toggle between seeing the single-line total and the breakdown by location? If so, you could work around this issue.

           

          Take a look at this attached demo:

          The calculated field I added to both data sources (and then used as a linking field) is:

          IIF([Show All], "All", [Location])

          • 2. Re: Blended Data Problem
            Lee Forst

            Thanks David.  This does work and I think I actually improved on it a bit.  See what you think?  All I did as add a Parameter (Locations) and then added a calc field to filter based on the selected Locations value (Location Filter).  Then I set your Show All parameter to True.

             

            By the way, I also realized I could just remove the CountD from the employee data source and got it to work.  This was one of the problems I had when trying to do this.

            Error "Cannot blend secondary data source ... unsupported aggregation" | Tableau Software

             

            But your method certainly would come in handy when I have cases when I have these types of aggregations.

            • 3. Re: Blended Data Problem
              Abhijeet Bijoor

              Even I was facing the same issue in Tableau 9.3 but only in few cases (its weird).

              The connecting column in the blend was a date field.

               

              Then I made a copy of the date field in both data sources and converted its data type to string and then connected it

              It started working for me (again weird).

               

              Thought of just sharing this point