7 Replies Latest reply on Nov 24, 2015 3:09 PM by Amr Shady

    Both aggregated and disaggregated data on same chart

    Amr Shady

      Help!  As you can see in the attached, I want to show a boxplot and the aggregated median of the bottom chart together.  Problem is that as soon as I use Analysis>Aggregate Measures, the below chart displays correctly, but the boxplot gets messed up.

       

      Any ideas how to resolve this?

        • 1. Re: Both aggregated and disaggregated data on same chart
          Simon Runc

          hi Amr,

           

          Looks like you've created your Box-and-Whiskers the old-skool way! Tableau now has box-and-whiskers, built in to the reference line/band.

           

          So on you're lower chart (the detail one), right click in the Y-Axis, and select 'Add Reference Line' -> Click on the Box-Plot icon. There you can add the Box-and-Whiskers over the existing plot. In here you can also select formatting...etc.

           

          Personally I'd leave 'Aggregate Measures' ticked, and use the Detail Shelf to determine the level of aggregation you want. For example, on superstore, if I want to look at Average Basket. I'd add Sales to the Row Shelf (my Axis), and then drag Order_ID onto the detail shelf. This tells Tableau, to plot me a mark for each 'Order ID'. If I wanted Average spend per customer. I'd create a measure SUM([Sales])/COUNTD([OrderID]), add this to the rows, and then add CustomerID to my detail shelf, as this time I want mark per customer.

           

          Hope that helps.

          • 2. Re: Both aggregated and disaggregated data on same chart
            Rody Zakovich

            Hello Amr,

             

            can you provide an example workbook?

             

            Getting the Most From the Tableau Forums

             

            We most likely just need to add a Lower Level Dimension to your Top Chart (Box Plots) so that aggregation is still happening, it just happens at a coarser level (I.e. it isn't really aggregated).

             

            Regards,

            Rody

            • 4. Re: Both aggregated and disaggregated data on same chart
              Amr Shady

              Thanks Simon, Rody.  Here's the packaged file. Stuck with 'Pers. Values 1' tab.  I tried to do some of your suggestions but still can't nail it.

               

              Appreciate your help.

              • 5. Re: Both aggregated and disaggregated data on same chart
                Amr Shady

                I think the problem for me is that the reference line should pull data from a different measure, not the same as the one on the shelf.  You will see a difference between the measures 'Personality Values' and 'Personality Values 3'

                 

                What I am trying to do is show the overall statistics of the sample, versus the median of the top x companies.

                • 6. Re: Both aggregated and disaggregated data on same chart
                  Simon Runc

                  hi Amr,

                   

                  So I've given you a couple of solutions...one 'hacky' one (which is the easy way), and then a slightly longer way (which is to build your own Box-and-Whiskers).

                   

                  So the simple way;

                   

                  I put your Measures back to aggregate, and used the [Org_Name], [First_Name], [Last Name] level you used. This just gives you control over the Level of Aggregation (btw you don't need to do this for this technique to work). I then Dual'ed the Axis, and changed the colour of the Box-Blot ones to 'White'. This way you don't seen them, but the box-and-whiskers are built from the [Org_Name], [First_Name], [Last Name] level. Told you it was a bit 'hacky'!!

                   

                  The other way, is to build your own. So for the PV 3, I've left the [Org_Name], [First_Name], [Last Name] dimensions on my detail shelf, but for the PV (the one we want to make the B-and-W for) I remove these. I then create calculated fields on PV, to bring back the Median, IQR, 0.25, 0.75 percentiles. I then bring these onto my detail shelf and use them to build a series of Reference Lines/Bands to create the Box-and-Whiskers. I have left the labels in, and not done any formatting, but with a bit of time they can look really good. B-and-W, out the Box only came in Tableau 8.3 (I think) but before that, this is how we'd build them!


                  You might also find this interesting

                  Fast box plots for large data sets

                  I couldn't use it here as we need the second Axis for your detail, but an interesting approach never the less

                   

                  Hope this all makes sense, but please post back if not.

                  • 7. Re: Both aggregated and disaggregated data on same chart
                    Amr Shady

                    So cool!! That did it for me.  I used the Hackey one   I remove the org_name, first name, last name from the details of Personality V3 and voila! I got the attached finally!  That Detail level aggregation was something I completed missed.  Very powerful.  Thanks for pointing it out Simon! Screen Shot 2015-11-25 at 1.05.20 AM.png