5 Replies Latest reply on Jul 31, 2012 1:03 PM by Jonathan Drummey

    required linking field introduces unwanted granularity in bar labels

    Zach Leber

      We have a project report that uses two data sources, one of which is an extract refreshed periodically and other is a live connection to our database which contains optional override statuses.  In order to blend data from the live connection we need to include a linking field in the view which prevents us from labelling the percentage bars with an absolute number.  So we're looking for a different way to relate the data or label the bars.  Again, we use bars to show relative percent of each project's samples in a given state, but we label the bars with the absolute numbers, which are very different project to project.  Some project have 20 samples, some 2000.  I've replicated the secnario using two Excel files as the data sources.

       

      Status 1.png

      Status 2.png

        • 1. Re: required linking field introduces unwanted granularity in bar labels
          Tracy Rodgers

          Hi Zach,

           

          One possible option, though I doubt you'll like it, is to manually remove or add the mark labels for the marks. You can right click on the mark and select Mark Label-->Never Show. Then, drag one of the labels to the end of the bar.

           

          Hope this helps a little bit!

           

          -Tracy

          • 2. Re: required linking field introduces unwanted granularity in bar labels
            Jonathan Drummey

            Hi Zach,

             

            I have a solution that gets the mark labels in the right place for the columns. It's based on the following process:

             

            1) Finding (or creating) one measure to return a value that can be used as a Gantt Bar, and a second measure to return a value for the label for that Gantt Bar.

            2) Making sure that those measures only return one value per bar, for example in your case, only one value per Project.

            3) Set up a dual axis chart with the original stacked bar chart measure and the Gantt Bar measure, with the label for the Gantt Bar measure set to the label measure.

             

            In general, table calcs make this pretty straightforward - see Andy Kriebel's post http://vizwiz.blogspot.com/2012/07/tableau-tip-adding-totals-of-2nd.html for an example. However, because the Sample Status field depends on the blended data, and we'd need that to partition the results, when I uncheck "Ignore in Table Calculations" that makes the table calcs break with a "Cannot find a total needed to compute a percentage. The database may have changed or a calculation cannot be completed. Try refreshing your connection or editing the calculation." error.

             

            So, the alternative is to build calculations from the ground up that will generate the correct totals for the # of Samples and # of Records, as well as the % of Total, that will only return one result per Project. I don't have time to go into all the details, the short version is that there are Feeder calcs that return a value for each row in the partition, then the final T Samples and T Records calcs use the LOOKUP() command to effectively partition on the Sample Status and Project. The Compute Using for everything is set to Sample.

             

            In your original views, you had a Grand Total column. I know of ways to hack the Grand Total for text tables and crosstabs, and I tried those and assorted variations on the Grand Total bars and nothing would work, i.e. none of the tests I know of to flag a Grand Total row so we can insert our own values work in the Grand Total bar charts. So, if you really want a Grand Total bar, unless someone has another idea my suggestion is to do the "create two worksheets and a dashboard" trick. For now, what I did was to create a "Total Samples" calc and use that as an extra header.

             

            I created two versions of the worksheet, one that uses a single axis with the Bar mark and on that the labels are not all right-aligned, for the second version I used the dual axis chart I described above and the labels are now all right-aligned.

             

            Cheers,

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: required linking field introduces unwanted granularity in bar labels
              Zach Leber

              Thanks Tracy, I would need the remaining mark label to be the total number of samples for the bar.  As it stands, all the mark labels say 1, because sample is on the LoD shelf.  And we also need this to be dynamic.  I have a feeling the answer will be to pre-join the data sources or come up with a table calc to determine the number of samples, but I don't how that value could then be put on the bar in a reasonable place.

              • 4. Re: required linking field introduces unwanted granularity in bar labels
                Zach Leber

                I was hoping you would weigh in Jonathan.  The results are what we are looking for, but as you mentioned in this recent thread, ultimately the better long-term solution may be to pre-join the data so the blended attribute can be used as a standard attribute.  Thank you very much for showing us what can be done otherwise and reminding the community that nothing is impossible.

                • 5. Re: required linking field introduces unwanted granularity in bar labels
                  Jonathan Drummey

                  Hi Zach, I'm glad to help, feel free to ping me anytime! And I agree, pre-joining is the way to go if you're able.