13 Replies Latest reply on Apr 23, 2012 9:24 AM by Connie Walker

    Bar graph with binning quintiles

    Connie Walker

      we are attempting to build a report that would “Bin” a rank ordered interval variable in equal quantities such as quintiles and show how that variable splits by month.  In other words I’d like a bar graph broken out in sections with the variable value range in one color for the top 20%, a different color for the next 20% and so forth with one bar for each month.


      I reviewed the discussion that Richard Leeke did about quantile and even with this I cannot get the bar graph to to be broken out as needed

        • 1. Re: Bar graph with binning quintiles
          Richard Leeke

          OK, I accept the challenge. 


          Will have a look tonight my time...

          • 2. Re: Bar graph with binning quintiles
            Richard Leeke

            Had a quick look at your data and you with that sort of volume of data you can actually achieve (roughly) what you described with reference lines. How far does this have to scale (i.e. what are your real data volumes).


            One downside with that approach is that you can't use a continuous date axis.


            Here are a couple of screenshots (2nd one is zoomed) showing what you can just do with point and click.


            Quintile Reference Lines.png


            Quintile Reference Lines Zoomed.png

            1 of 1 people found this helpful
            • 3. Re: Bar graph with binning quintiles
              Connie Walker

              Thanks for your help. 

              The data that I sent is actual values we will be using so the values does have a broad range

              I thought doing a reference line would work too but when I select "Value" as disaggregate measure I cannot add a reference line for each of the month (I can however do a reference line for the value  Y Axis - it does allow me to do the reference line but i need the reference line to be different for each month)


              How were you able to add reference line to the date axis? SOrry for being such a newbie on this... i have only been using Tableau for about a month

              • 4. Re: Bar graph with binning quintiles
                Richard Leeke

                > Sorry for being such a newbie on this...


                You don't have to apologise, we all started sometime.


                Look at the screenshot below.  The key thing is to make the date pill discrete, that allows you to get a reference line per month by selecting Per Cell. that means the gaps for the weekends get compressed on the chart. If you don't like that, I think you have to go with the table calculation approach.  I'll have a quick go at putting that example together tonight, (if I remember).


                Ref Line Settings.PNG

                • 5. Re: Bar graph with binning quintiles
                  Richard Leeke

                  I've attached a workbook showing a few ways of representing this - including one based on the quantile table calculation function I posted a while back (which requires a copy of the calculation for each quantile rank you want to show), plus a slightly different approach to displaying multiple quantiles. 


                  The one showing 0, 20, 40, 60, 80 and 100 percentile reference lines was intermittently not displaying properly for me just now. It should have different coloured bands visible. If it's just white space with a few dots and lines, try selecting edit reference line and then don't change anything and see if it redisplays properly. Not sure what's going on there.


                  Both the table calculation approaches make use of a parameter controlling the precision of the answer (that is just a floating point number - so 10 means rounded to the nearest 10, 0.01 means rounded to 2 decimal places, etc). The higher precision you select, the longer the calculations take to evaluate. What I normally do is set the precision relatively low while I'm exploring the data (say 10 with your data), then when I want to publish the results maybe set it more accurately. With your data volumes you can actually get away without the rounding - though it takes 20 seconds or so to refresh the views, whereas with rounded data the visual effect is almost identical but the screen refresh is virtually instant.


                  On the quantile rank view, you can select the quantile you want (percentile, decile, quintile, quartile, etc). For the work I do I find the percentile rank "heat map" (below) a really effect view.


                  Table Calc Quantile Rank.png

                  • 6. Re: Bar graph with binning quintiles
                    Connie Walker

                    Thanks - this is exactly what i needed. 

                    • 7. Re: Bar graph with binning quintiles
                      Connie Walker

                      A related question (please let me know if I should post a new thread)


                      I am trying to do a crosstab for these data such as

                      Crosstab Sample.JPG


                      Any suggestion on how i can get this view - of course right clicking on the heat map tab and selecting duplicate as crosstab does not give me the data i need

                      • 8. Re: Bar graph with binning quintiles
                        Richard Leeke

                        I've added a couple of sheets showing how to get tabular monthly data using either the individual quantile calculated fields or using the quantile rank approach. I just duplicated the two views using those approaches and reformatted a bit.


                        A couple of points.


                        I don't understanjd why you have the max and min columns under each month. Ive just shown the max and min along with all of the quintile values in the one column.


                        Qunintiles are the numbers that divide the points into 5 equal sized sets, so there are only 4 quintile values (which are the same as the 20th, 40th, 60th and 80th percentiles. The 0 and 5 entries correspond to min and max.


                        Does that make sense?


                        Note that you cab experiment with the impact of the precision parameter on the accuracy of the results (you may have to change the number formatting to see decimal places). Note that you can also select whether you want quartile, quintile, decile or percentile.

                        • 9. Re: Bar graph with binning quintiles
                          Connie Walker

                          Thanks Richard - this is a great starting point for me.


                          To answer your question I need the Min and Max of the range in the quintile - here is a sample graph that we eventually want to produce.

                          Sample Graph.JPG



                          Hope this cleared up your question

                          • 10. Re: Bar graph with binning quintiles
                            Richard Leeke

                            OK, I see what you want - I'll have a play and see if I can work out how to display it like that.


                            Just a couple of clarifications.


                            Firstly (and this me being a bit pedantic), as I said before, the quintiles are the values at the boundaries between the sets of 20%, not the bands. So the crosstab I gave you in the last postings have all the values you want: The lowest 20% goes from the overall minimum to the first quintile, the next band goes from the 1st quintile to the 2nd qunintile, etc.


                            The other thing is you seem to have a special bonus offer in April - you get an extra free quintile. I don't think I can get Tableau to make the numbers add up to 120% (or at least I'm not prepared to try). 

                            • 11. Re: Bar graph with binning quintiles
                              Richard Leeke

                              OK, you've got me scratching my head a bit here a bit here - I can't immediately see a way to do this.


                              What you want is a Gantt bar with the start of the bars defined by the quintile values and the length of the bars defined by the difference between the adjacent quintiles.


                              Trying to do that with the version where I have individual calculated fields for each quintile runs into the problem that a calculated field can't refer to the value of the [Measure Names] special field.


                              Trying to do it with the quantile rank version runs into the problem that table calculations can't partition on the results of another table calculation (in this case it needs to reference quantile rank).


                              Let me ponder for a bit...

                              • 12. Re: Bar graph with binning quintiles
                                Richard Leeke

                                Here you go - just needed another table calc...


                                I'm glad you persisted with the question - that's ended up being a really useful view.  I especially like the fact that you can just select the quantile you want with a parameter and get your bar broken into the right number of sections. The legend could probably do with a bit of tweaking to make it more user-friendly, but other than that it looks really good, I think.


                                Here it is (zoomed in because the outliers obscure the detail) for quintiles:


                                Quintile Gantt Chart (zoomed).png


                                and here for deciles:


                                Decile Gantt Chart (zoomed).png

                                • 13. Re: Bar graph with binning quintiles
                                  Connie Walker

                                  Thanks Richard!!!! You have helped A LOT!!! This is great -