1 2 Previous Next 16 Replies Latest reply on Jul 30, 2018 8:23 AM by Don Wise

Create Bins for Count of Occurrance

Hi,

I'm wondering if anybody can help me to crack this challenge I've got,

I have a huge list of bookings (we are a travel company),

Each unique customer has a unique Customer ID.

A customer can of course book more than once (repeat traveller).

I need a way to put into Bins, the Counts of the Customer IDs,

So I need bins which are for example:

Travelled once (Customer ID appears once in period)

Travelled twice (Customer ID appears twice in period)

Travelled 3-5 times

Travelled more than 5 times

In a given year.

I hope that makes sense. I can create a calculated field to count the occurrences of an individual Customer ID - however I cannot create Bins off the calculation to group the data into those who have travelled once, twice etc.

My data looks something like this

 Booking Number Customer ID (Count of Customer ID) 1234566 Cust_208734122 4 1234567 Cust_208734122 4 1234568 Cust_208734122 4 1234569 Cust_208734122 4 5234567 Cust_208734126 3 5234568 Cust_208734126 3 5234569 Cust_208734126 3 3655886 Cust_208734129 2 3655887 Cust_208734130 2 6655448 Cust_208734131 1

Any help would be greatly appreciated.

Thanks,

Jordan

• 1. Re: Create Bins for Count of Occurrance

Hi Jordan,

See if the attached helps? I needed to add a couple of transactions to get to the counts you were looking for.  But you get the idea.  You don't really need a Count of Customer ID for this. Thx, Don

• 2. Re: Create Bins for Count of Occurrance

Hi Don,

Thanks for your time on this,

The calculation works and successfully counts each ID and how many times they have travelled,

The only thing is I would need to have Customer ID as a field in the view,

I have I think 350,000 IDs in the data, so what I really need to be able to do is have them grouped.

So if I leave Customer ID out of the view it seems to think everybody has travelled more than 5 times, I wish!

Any ideas?

Thanks,

Jordan

• 3. Re: Create Bins for Count of Occurrance

Hi Jordan,

I see what you mean. See if the newly attached and two examples below work for you?  Both rely on an LOD calculation, but simply represents your data in two different ways yet achieving what you might be looking for.  Thx! Don

1 of 1 people found this helpful
• 4. Re: Create Bins for Count of Occurrance

Hi Don,

Do you mind doing me a big favour? I'm on lowly 10.5, so I can't open your workbook.

Do you mind converting it to 10.5 please?

Thanks,

Jordan

• 5. Re: Create Bins for Count of Occurrance

Hi Jordan,

No worries...newly attached 10.5  Thx, Don

1 of 1 people found this helpful
• 6. Re: Create Bins for Count of Occurrance

Thanks!

I think we're very close and it looks good,

The only thing is my data doesn't include the Count of Customer ID as a column that was done as a calculation in Tableau - sorry if that was misleading - so basically I have a calculation which is COUNT([Customer ID])

When I try to use it in the LOD calculation you've made it comes back with cannot mix aggregate and non-aggregate

Is that something that's easily fixed and we can work around do you think?

Thanks,

Jordan

• 7. Re: Create Bins for Count of Occurrance

Hi Jordan,

I just created a new Tableau Calculation to match yours and then inserted it into the LOD.  Take off MIN and it should work!  Thx, Don

1 of 1 people found this helpful
• 8. Re: Create Bins for Count of Occurrance

Fantastic,

Thanks a lot.

I think the final piece of the puzzle is that the bins aren't necessarily working perfectly,

The bins are only in individual bins of 1, as opposed to being grouped as we specify in the Number of Trips calculation, so it ends up looking something like this where it's only in individual bins of 1 if that makes sense?

Any way to group them up?

Thanks a lot for all of your help,

Jordan

• 9. Re: Create Bins for Count of Occurrance

Hi Jordan,

If that resolves your issue, could you close the thread by marking it as correct ?  Thx, Don

• 10. Re: Create Bins for Count of Occurrance

You might get better results by taking the 1st sheet in my example and changing it from a bar chart to a table.  Or rearranging the second sheet as per the example(s) below?  Hard for me to tell what's going on without actual data...

• 11. Re: Create Bins for Count of Occurrance

Hi Jordan,

Try following the below screenshot.  It will be a histogram for the first one, the second one is simply duplicating the histogram to a cross tab.  Maybe that'll help with the amount of data you have and a need for better grouping?  If you need help with totaling on the histogram, I can provide a walk-through on how to do that.  Thx, Don

1 of 1 people found this helpful
• 12. Re: Create Bins for Count of Occurrance

Hi Don,

Thanks a lot,

I’m away from work now for the weekend but I’ll be sure to check first

thing Monday and get back to you!

Cheers

Jordan

• 13. Re: Create Bins for Count of Occurrance

Hi Don,

Thanks for this it's looking great,

I appreciate it must be hard without the data.

It's looking something like this now.

How do you mean totalling for the histogram?

Thanks!

Jordan

• 14. Re: Create Bins for Count of Occurrance

Great!

What I mean for totaling on a histogram, if there's a need to show the value at the top of each column, then under Analytics I add a Reference Line per Cell as a Sum of (COUNTD([CUSTOMER ID]).  Then format the value to rest in the middle of the column.  Discrete pills won't allow for Grand Totals.

1 of 1 people found this helpful
1 2 Previous Next