4 Replies Latest reply on Jun 25, 2017 2:08 PM by Mark Ackerman

    Split Sorted Chart Onto Multiple Worksheets

    Mark Ackerman

      I create a sorted bar chart which has about 90 rows for various clients (the number of rows varies a bit from client to client).  Our clients view the reports in PDF format, so the chart needs to be split onto 3 worksheets so that it fits on the PDF.  Currently I am using a filter to manually select the 35 highest scores for worksheet 1, for worksheet 2 I manually filter for the next 35 highest scores, and worksheet 3 is filtered to show the remaining rows.  This manual process is not ideal.  I am wondering if anyone knows how to automate this.

       

      There is also another bar chart in the report that is sorted by the same variable as the first chart and needs to be split the same way. 

       

      I have attached a Tableau workbook to help clarify what I am looking for.  The actual production charts have many more columns in them, but this workbook should work as a simplified example.

        • 1. Re: Split Sorted Chart Onto Multiple Worksheets
          Vasil Petkov

          Hi Mark,

           

          You can leverage sets to dynamically display top, 2nd top and remaining. In the attached I created a top 35 set, then a top 70 set and then a combined set of these two which gives the top 70 without the top 35 effectively filtering for next top 35. The remaining can simply be shown as out of top 70.

           

          Hope this helps.

           

          Best,

          -V

          • 2. Re: Split Sorted Chart Onto Multiple Worksheets
            Mark Ackerman

            Thanks for the suggestion of using sets for this problem.  I had not thought of that.  The solution worked great with the simplified report mockup I attached, but I was having some problems with it working with the actual more complex report.  I created a more realistic mockup and am playing around with it a bit.  I'll let you know soon what I find.

            • 3. Re: Split Sorted Chart Onto Multiple Worksheets
              Mark Ackerman

              There were two adjustments I had to make so that this solution would work in my more complex production workbook.

               

              In the production workbook, I have a filter called Community that allows for the selection of one community out of many that are in the database.  The problem I was running into was that the top 35 filter was selecting the top 35 question scores across all communities and then showing within that set the question scores that applied to the selected community.  So the top 35 filter might only show 10 question scores that were both top 35 within the entire database and also belonged to the selected community.  I was able to fix this by right clicking on the Community filter and selecting "Add to Context".  This made it so that Top 35 was calculated after the Community filter was performed, and now works correctly in the production workbook.

               

              The other issue I was trying to solve was how to make sure the Score 1 worksheets were sorted the same way as the Score 2 worksheets.  I was concerned that tied question scores might not sort in the same order for both worksheets.  For example, if 3 questions had the same score, would they sort in the same order in both worksheets.  I created a custom field composed of the combined question score and the question text to force the sort to be the same for both worksheets, but Tableau was not sorting this field appropriately.  So I removed the custom sort field and just sorted by Score 1.  It looks like this will work for me, because it seems that Tableau defaults to sorting in data source order when the scores are tied, so the sorting should be consistent across worksheets.

               

              Your solution is very helpful.  Thanks Vasil.

              • 4. Re: Split Sorted Chart Onto Multiple Worksheets
                Mark Ackerman

                Hi Vasil,

                 

                The specifications for the problem you helped with me before changed a bit, so I thought I would see if you have a solution to that problem as well.  Previously you helped me split a long sorted page into three separate worksheets that would each fit on a printed page.  You utilized sets as filters to achieve this.

                 

                The tweak I need is to add another sorting level, and then filter the rows so that the first 35 appear on the first worksheet, the 2nd 35 appear on the next worksheet, and the rest of the rows appear on the last worksheet.  FYI - Both sort levels are using the AVG(Score), just aggregated at different levels.  The range of these averages will always between 0 and 100 inclusive.

                 

                I can submit this as a new problem if that would be helpful.  I have attached a new workbook showing the new sorting level on the worksheet "All with New Sort Level".