14 Replies Latest reply on Jul 15, 2013 3:54 AM by Siraj Samsudeen

# How to create bins at a rolled up level

I have a denormalized Orders table which contains Order Header ID, Order Line ID, Amount, etc. I want to create a report that shows the number of order below 1000 USD, 1,000-5,000 USD, 5,000-10,000 USD and above 10000 USD. How do I achieve this in Tableau?

• ###### 1. Re: How to create bins at a rolled up level

Siraj:

There are many ways to accomplish this, if I am understanding what you want.  You can write a calculation for each scenario, like

if countd([OrderTotal]) < 1000 then 1 else 0,

and

if countd([OrderTotal]) > 1,000 AND countd([OrderTotal]) <= 5000 then 1 else 0, etc.

Do that for each level, and the sum of the numbers will give you what you're looking for, I believe.  I'm sure there are other, and possibly, better ways to accomplish this, as I am fairly new--but I hope this helps.

1 of 1 people found this helpful
• ###### 2. Re: How to create bins at a rolled up level

Hi Matthew, thanks for your answer. I also thought of the calculation like the one you suggested, but the calculation has to work on the Order Header level whereas the table contains records at Order Line level. For example, if Order No.1 has 4 line items, the calculation has to sum up the amount of the 4 line items. So, I guess I need to use a table calculation with a partition on Order Header ID, or something like this. But I am not that familiar with table calculations yet, that is why I posted.  I also wanted the bins to be dynamic rather than hard-coding them in calculated fields.

• ###### 3. Re: How to create bins at a rolled up level

I would think the calculation would be dynamic, in that any future value would fall into your categories.  Not sure what you mean.

Your best bet is to post a packaged workbook with a sample set up or the attempts you've made, or include a mock-up of what you're looking for with your packaged workbook.  I am certain what you are wanting can be done and I'm not sure any table calcs are needed on this one.  Hopefully someone else understands your problem better and can help

• ###### 4. Re: How to create bins at a rolled up level

Siraj,

I think this is what you are looking to do.  Look at the second part

Breaking BI: Creating Histograms in Tableau

Thanks,

Associate Consultant

Mariner, LLC

1 of 1 people found this helpful
• ###### 5. Re: How to create bins at a rolled up level

Nice blog Brad! (But that jitter has to go, made my eyes twitch and almost got sick.)

--Shawn

• ###### 6. Re: How to create bins at a rolled up level

jitter?

• ###### 7. Re: Re: How to create bins at a rolled up level

Hmmm, I thought that was an intentional effect. See attached. When I'm not recording it, it is much faster and disturbing.

--Shawn

• ###### 8. Re: How to create bins at a rolled up level

I think that's an issue with your browser.  Might want to try a diff browser.

• ###### 9. Re: How to create bins at a rolled up level

I don't get it on Chrome

• ###### 10. Re: How to create bins at a rolled up level

Alex, I did a little testing. Since I work at 1920x1080 resolution, I use Ctrl-Scroll Wheel a lot, so I can get fonts big enough to read. At 100% the jitters disappear. But anything greater than 100% and they appear. What happens when you zoom in?

FYI: I posted a Chrome support case (can't imagine I'll get an answer anytime soon, too big a company).

--Shawn

• ###### 11. Re: How to create bins at a rolled up level

Sorry for being away and not responding to the helpful answers. I was on holidays for a few days and then could not get back immediately as I wanted to create a sample file which does not expose our internal data. Today, I will create a sample file and post it along with a tableau workbook to show you clearly what I am trying to do.

When I say dynamic, I mean that the bands have to be dynamic. Meaning, I don't want to create a calculation hard-coded to the bands that I need today, say  0 to 500 and 500 to 1000. Tomorrow, if my bands have to change to 0 to 5000, 5000 to 10000, then I have to keep updating the calculations, which is not a good thing. I need to figure out a way to create dynamic bands like histogram creates by examining the data.

• ###### 12. Re: How to create bins at a rolled up level

Sorry for the delay in getting back to you. I read through your post and I think that there is a similarity between the problem I am trying to solve and the histogram with profit by customer bin that you have created. Thanks a lot for taking the time to document the steps. There are a few questions that I have regarding the steps and the output you arrived at. I @think that it is more appropriate if I post these questions in your blog as anyone who visits that page can see the questions. Thanks once again for taking the time to answer.

• ###### 13. Re: Re: How to create bins at a rolled up level

Hi Matthew,

Please find attached the XL file which contains some sample data to illustrate the problem and the Tableau file where I am trying to create the histogram. Currently, the histogram displays the amounts at the Line Item level, but I want it to display the amounts at the Order Level. The histogram all the 28 line items but the total number of orders in the source file is only 14 orders. Once this part of the problem is resolved, then I can look at creating a dynamic bin size using parameters as demonstrated in the blog post by Brad

• ###### 14. Re: How to create bins at a rolled up level

Anyone here who can give me some ideas about how this can be done in Tableau?