4 Replies Latest reply on Nov 9, 2016 2:55 PM by Dan Burrell

    Can I identify easily the members of a blend that don't meet the blend criteria?

    Dan Burrell

      Hi everyone.

       

      Quite simple I want to do the following.

       

      My data looks something like this:

       

      Customer IDProduct IDProduct Name
      1234ABCCar
      1234ABCDECar Insurance
      1234FGHIHouse

       

      I have a set of customers that I want to filter based on a product they actually have ( say a Car, a house....)

      Then I want to filter those very same customers based on a product they DO NOT have ( such as Car Insurance, House insurance etc....)

      So in the above data, a user could filter by those who DO have a house, but don't have House Insurance.

       

      What I anticipated I could do was to use a data blend on the same dataset....with the relationship being the CustomerID and using the following mechanism to find the ones that have (a Car) in my primary dataset

      but do not have insurance in the secondary dataset. (the nulls)

       

      isnull([Customer_Identifier])

      and [Filter - Customers does not have]  //<-this is a parameter to switch the logic on and off. ( it's not always desirable )

       

      So where ever my primary dataset customers have a car, but where my secondary dataset customers DO NOT have a relationship, I want those customers.

       

      Am I doing this right? It appears the blend is only working where the customer number exists in both datasources

      I thought about using sets, but they can't be altered once in motion it appears

       

      I don't want to falsely exclude records and I want the user to be able to interact with BOTH filters to custom build their Customer list. ( Customer has: XYZ and Customer does not have: ABC )

       

      I'll create a dummy workbook shortly to exhibit the problem.

        • 1. Re: Can I identify easily the members of a blend that don't meet the blend criteria?
          Nick Merritt

          Dan - What version of Tableau are you using? If you're using 9.x or earlier then your options are more limited, whereas if you are already on v10 I think that you might have an easier time solving for this use case with joins rather than via blending.

           

          One question - it's also not clear what the difference is between table 1 and table 2.

          Your sample indicates for your first data source, there will be a record for every product owned by every customer (multiple products per customer).

          If your second data table has records for every customer for every product they don't own, then I am not sure how you want to define the relationship between the product(s) not owned and those owned.

           

          Please advise and we will try to make a sound recommendation, whether it's a join or a union.

          • 2. Re: Can I identify easily the members of a blend that don't meet the blend criteria?
            Dan Burrell

            Both tables are actually the same ( So i was attempting to blend the data upon itself as a solution, with it in mind that may not be. )

            To be clear I have one datasource with customers and their products.

            The sample was an attempt to show what my data looks like.

             

            I am using Tableau 10.0 as well.

            I am unsure how how to identify customers based on both products they do have an the products they "could have" but currently don't

             

            I had assumed if I related the data to itself, those with some products would have nulls when blended with data on a different set of products and therefore could be positively identified. ( and followed up )

            I crux of the problem is that i want the list of products to be able to be customised by a dashboard user ( which makes it more complicated )

             

            thanks for your reply. I appreciate this excellent community!

            • 3. Re: Can I identify easily the members of a blend that don't meet the blend criteria?
              Nick Merritt

              Dan - Thanks for the reply. One more question. When you were trying to design a solution based on blends, what in the data indicates whether a particular customer owns or does not own a particular products?

              • Is there a row for ever customer and every product irrespective of whether a customer owns that product...
              • ...or does the table only contain rows for products owned by customers?
                • If the latter, is there a master list of products that could be used as a reference against the original table of customer-owned products?

              • 4. Re: Can I identify easily the members of a blend that don't meet the blend criteria?
                Dan Burrell

                Hi Nick.

                 

                When i was trying to design a solution based on the blends, the presence of a null in secondary datasource of the customer_id indicated that the customer did not have the product.

                So Customer_id  1234 - Customer_id (null)

                However this appears to only work when the filtering was applied at datasource level ( which can't be changed by an interactive user )

                 

                The customer only has a row for each product the customer has

                I could bring in a master list of products reasonably easily ( Using an extract I imagine which could be kept upto date )

                 

                Thanks for your reply!