2 Replies Latest reply on Jan 23, 2018 4:56 AM by Rien Heidt

    Comparing non-like values (Samples and Sales)

    Rien Heidt

      We have a list of clients who sample our Top Products and a list of clients who have purchased any level Products and we're trying to see the correlation between sampling and sales. However, the products are not a 1-1 match. Samples get reported out as "Apples" or "Oranges" or "Bread" and purchases are reported out as "Granny Smith", "Pink Lady", "Florida Orange", "Sourdough", "Rye", "Pumpernickel", etc. We want to dynamically be able to select both the samples and the purchases ("I want to see everyone who sampled ("Apples" or "Bread") OR bought ("Granny Smith", "Rye", or "Sourdough")" etc etc etc). I can't use parameters since it's multi-select. I can hard-code things, but the goal is to make a dynamic workbook where they can set the Samples to anything they'd like and set the Purchases to anything they'd like and see a list of clients who Sampled but did not buy, clients who bought but did not sample, and clients who sampled and bought. They'd also like to run counts (how many the client sampled and bought), and percentages (of the clients who purchased "Granny Smith", "Rye", or "Sourdough" how many also Sampled "Apples" or "Bread"?)

       

      Does anyone have any suggestions for how to do this or if this is even feasible?

       

      I have a packaged workbook that just contains a mock-up of Samples and Purchases data. I can open up to Tableau 10.4, but I don't have 10.5. Thanks!

        • 1. Re: Comparing non-like values (Samples and Sales)
          Jim Dehner

          Hi

           

          is this the sort of thing you want

           

           

          I did this by grouping the specific "purchase Items" and naming the Group as shown

           

           

          If there were a lot of these I would recommend creating a file with sample item and purchase item cross match then joining it with your data

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Comparing non-like values (Samples and Sales)
            Rien Heidt

            No, this still doesn't let me filter the way I need the data to filter, nor does it give me a sample count. In this workbook (as with mine), if I filter to Apple and Orange, or filter to Rye and Pink Lady, I lose the selection of clients who don't overlap - and I include those who sampled Bread, but I don't want those who sampled Bread, just who sampled Apple or Orange, or who bought Rye or Pink Lady (in this example). Then how many customers who bought Rye also Sampled Apple? What percentage of those who Sampled did not buy anything?

             

            But it also needs to be dynamic enough to compare against ANY Sample to ANY product -we need to limit our data set dynamically to anyone mix of Samples and Products. And the Samples can be Apple1, Apple2, Apple3, Orange, Bread1, Bread2. So if Pink Lady is my new apple type, and I know that people who buy apples almost always get Rye, I may want to see all my customers who bought Pink Lady or Rye and compare them to any clients who sampled from Apple3 or Apple2, but I don't want to see any one who didn't purchase or sample from those selections. Then my boss is really interested in seeing what the correlation between Sampling Orange is to our entire apple line - Pink Lady, Granny Smith, etc. Can we structure our data so we can both look at the same data set, filter it to what we want to see, and still run queries against that smaller, filtered set.

             

            After I posted this yesterday, we worked on it for a while and are coming to the conclusion that because we need to do summary calculations and percentages, we really need to break this down inside the SQL, which is frustrating, because it doesn't deliver the dynamic analysis we were hoping for.