3 Replies Latest reply on Jun 2, 2016 10:47 AM by Joe Oppelt

    Help needed in finding the frequencies of combinations..

    deepesh.moad

      Hi Guys,

       

      I need you help in finding the frequency of the combinations in a partition,somewhat like Mkt. Basket

       

       

      ItemWeek
      Milk1
      Bread1
      Egg1
      Cookies1
      Milk2
      Bread2
      Egg3
      Cookies3
      Doughnut3
      Bread3

       

      For example:

      Unique Items

      Week 1 has Milk, Bread, Egg, Cookies
      Week 2 has Milk, Bread
      Week 3 has Egg, Cookies, Doughnut, Bread
      Frequent combinations are
      Milk,Bread which came 2 times out of 3 weeks
      Egg, Bread which came 2 times out of 3 weeks

       

      I am not looking  to do a cross join,as actual data is huge.

      How can we achieve this in Tableau?

       

      Thanks a ton!

        • 1. Re: Help needed in finding the frequencies of combinations..
          Joe Oppelt

          Is the attached what you are looking for?

           

          In your original sheet I added a calc to window_count the number of times a particular [Item] shows up on the sheet.  (I did advanced settings to tell Tableau to restart this for every Item.  Otherwise you get only one value of 10 because there are 10 rows on the sheet.)

           

          In Sheet 3 I distilled the viz down so we get only one row per item.

           

          In Sheet 4 I modified that to sort the counts.

           

          If this is what you need, we can discuss the details of what I did in there.

          • 2. Re: Help needed in finding the frequencies of combinations..
            deepesh.moad

            Thanks Joe for the reply.

             

            Actually, I am looking something like this.Want to show the combinations like below,as we do for market basket analysis .

             

            I know I can achive this using R,but I don't want to use it.

             

            Unique Items

            Week 1 = Milk, Bread, Egg, Cookies
            Week 2 =Milk, Bread
            Week 3 = Egg, Cookies, Doughnut, Bread

            Frequent combinations are

            Items        Occurrence         #Weeks

            Milk,Bread     2 times out of 3 weeks
            Egg, Bread which came 2 times out of 3 weeks

            Thanks,

            Deepesh

            • 3. Re: Help needed in finding the frequencies of combinations..
              Joe Oppelt

              What you are asking for might look simple, but it's going to be very complicated.

               

              For one thing, you are looking for iterative processing, but tableau doesn't have any sort of a loop construct.  To get (what I assume can be) an unlimited set of values strung together and separated by commas, you'll need to loop through all the week-1 rows and add to a string calc field, and then through all the week-2 rows, etc.  So making the string to display the names of all the items in a week is one problem.

               

              Listing the top combinations is even messier.  It looks like a particular value can occur anywhere in the order of items.  So to see if "Bread and Eggs" showed up in any week (or "Eggs and Bread", if you want to treat them as equals!) you'll need a bunch of nested looping.

               

              The way I can see to simulate that is to have a complex nested IF structure that encompasses all the possible combinations.  It will be messy.  It will be tedious.  But it can be done.  And if you ever add a new item into the list of possible items, the combination of IF possibilities will grow geometrically.  (If not logarithmically?)

               

              To eliminate the problem of item ordering in the list for the week, I would consider making a calc for each item that looks something like this:

               

              COUNT(if [Item] = "Bread" then [Item] end)

               

              Then, for each week, if you have at least one "Bread" the calc will be greater than zero.  In the nested IF calc, process these calcs, always in the same order, and whether "Bread" shows up first or last, you'll know "Bread" is in there somewhere, and it will be easier to assess the combinations.

               

              So what I offered earlier tells you that "Bread" shows up x-many times.  What you really need to know is that "Bread and Eggs" showed up x-many times, and "Bread, Milk and Cookies" so many times.  And all possible mixes of combinations.  And then rank those, and display a list of Top-N occurrences of combinations.

               

              You might want to consider pre-processing your data set with some actual programming utility.