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

# Help needed in finding the frequencies of combinations..

Hi Guys,

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

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..

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..

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 areItems        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..

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.