13 Replies Latest reply on Nov 8, 2018 4:55 AM by Paul Wachtler

# Question on possible basic table function.  Maybe not basic.

I'm looking for three histograms in one chart that uses what I would imagine is a table function.  The data looks like this:

The histogram bins should be on value and show the % won = sum(won)/count(won) like this:

And there should be 3.  One for each color.  Here's the closest I've come.

• ###### 1. Re: Question on possible basic table function.  Maybe not basic.

I shoudl mention it's not a histogram in that the sum of the bars dont' equal to 1 or to the sum of the total.  It's more that there are convenient bins and I'm looking for % wins by bin

• ###### 2. Re: Question on possible basic table function.  Maybe not basic.

Hi Morton,

From your data, I believe that the "rand" field is the color you're talking about.

I'm not sure if you mean that you want one bar chart with bins but the bars broken out by color - if that's what you want, you should be able to drop the "rand" field onto your color mark to separate the bars by color.

If you want three separate rows with a bar chart with bins for each, drop the "rand" field onto your rows shelf - make sure it's discrete and not continuous.

Let me know if you have any questions.

Best,

Paul

• ###### 3. Re: Question on possible basic table function.  Maybe not basic.

I want the latter.  I guess I can drop it onto the rows shelf and make it discrete and not continuous.  However,that will result in three separate graphs, not three series on one graph.

Further, how do I get the percentages.  As you can see, I have totals that have to be aggregated and % calculated from them

• ###### 4. Re: Question on possible basic table function.  Maybe not basic.

Hi Morton - I'm not quite following your requirements here.  Do you want one chart, or do you want separate rows?  Separating the rows will separate the chart.

I don't understand what you mean by "I have totals that have to be aggregated and % calculated from them" - What totals are you calculating and outside of Tableau, how are you calculating your percentages?  If you can provide me with more information on the calculations you intend to perform, and how you ultimately want the chart to look, I'll be better able to assist.

• ###### 5. Re: Question on possible basic table function.  Maybe not basic.

Here is an excel sheet with the data and the graph exactly how I want it.  You can see a lot of hand manipulation is required to make it, I hope tableau can to better

• ###### 6. Re: Question on possible basic table function.  Maybe not basic.

So all of the same calculations you have in Excel can be done in Tableau.  I don't understand your Random (Rand) field - I know you're using that for color, but why are you splitting the data into three random values/colors?

Anyway, you can use the same calc for creating your bucket bins - round([value]/25000,0).  Then to figure out the percentages I believe you can use an LOD:

{fixed [rand], [bucket] : sum(won)/count(won)}

Then drop [bucket] on your columns shelf, your percentage field on the rows shelf, and put your rand field on the color mark.  That will create the bars the way you want them.

Best,

Paul

• ###### 7. Re: Question on possible basic table function.  Maybe not basic.

Hi Morton,

Just trying this with a set of fresh eyes - is this what you are looking for ? I have adjusted the viz so that the percentage distribution of 1,2 and 3 is inside each bin range.

HTH 18.2 attached

Peter

• ###### 8. Re: Question on possible basic table function.  Maybe not basic.

This isn't the correct answer.  You're calculating the subtotal of the sum of a rand color as a % of the grand total for that bin.  What I'm looking for is take all the rows that are in that bin call that A.  count the number of rows where wins = 1 and call that B.  calculate B/A as a % for that bin for that rand.  That's what I want the y value to be.  You can see that's how I did it in excel. If you can get your tableau to match my excel, that would be the right answer.

Thanks so much for your help

• ###### 9. Re: Question on possible basic table function.  Maybe not basic.

I split it like this becaues its a dummy variable in my dummy table that shares the same structure as my real table.  If you can do it with the dummy table, I can copy that solution for my real table.

your lod answer was what I was looking for, but with a twist.  I made the bins artificially in excel.  But I want tableau to auto make the bins the way Peter made the bins.  Tableau will probably call that Value (bin).  I'm actually not sure how he did it.  Anyway, then I want to make {fixed [rand], [Value (bin)] : sum(won)/count(won)}.  I understand this solution conceptually but I can't implement it practically because I don't know how to make the bins wihtout selecting the histogram in the showme window and how to enter a lod and how to make an lod referencing Value (bin) after I figure out how to make one.

If you could push this over the line, this would be the solution

• ###### 10. Re: Question on possible basic table function.  Maybe not basic.

Just replying to the bins thing, I didn't do anything. This was how it was setup in the .twbx attached to the original post.

Peter

• ###### 11. Re: Question on possible basic table function.  Maybe not basic.

Hi Morton,

Tableau can auto make bins for you, but in this case, if you use that same calculation that you used in excel (round([value]/25000,0) in a calculated field, you can add that to your column shelf and it will split the bars accordingly.  You don't need Tableau's built in bins function in this case.

If you'd really rather use Tableau's built in bins functionality, right click on your [value] metric and select "Create" > "Bins".  You can then set your bin size there.

Best,

Paul

• ###### 12. Re: Question on possible basic table function.  Maybe not basic.

Great. What’s the right syntax on the load after the bins are made by tableau. Is it actually [Value (bins)]?

• ###### 13. Re: Question on possible basic table function.  Maybe not basic.

To bring the bins field onto your viz, if you use Tableau's built in bins functionality, then by default it will be named [Value (bins)].  However you can rename this to anything you'd like.

For the LOD, if you keep the bins field name as [Value (bins)] then it would be:

{fixed [rand], [Value (bins)] : sum(won)/count(won)}