8 Replies Latest reply on Aug 13, 2018 3:55 AM by Martin Asztalos

    Dynamic upper y-axis range for a boxplot based on a LOD calculated field

    Martin Asztalos

      Hello,

       

      I am new to Tableau but already made some nice box-plot charts (almost).

      We have grouped our products in a LOD function and showing the data in a box-plot chart (using the calculated field as a filter).

      However, the Q3 range of boxes for different groups is quite large and therefore a fixed upper range is not applicable.

      Therefore I would like to use a dynamic upper y range axis from a calculated field.

      So, when I go (slider) through the different groups I want that the upper y axis range value gets changed automatically based on the value from the previously calculated field.

       

      Can someone please help me with a step by step approach how enable a calculated field for a dynamic upper y range axis for a box plot chart?

       

      Kind Regards

      Martin

        • 1. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
          Martin Asztalos

          Hi,

           

          Does have someone an idea how to solve this????

           

          Kind Regards

          Martin

          • 2. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
            Jim Dehner

            Good morning Martin

             

            Please understand your question is important but without seeing the actual TWBX workbook with at least dummy data it is going to be difficult to get a response  in the detail you want

            Please attach a workbook

             

            Jim

            • 3. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
              Martin Asztalos

              Hi Jim,

               

              Thanks for your answer ,

              I will prepare a TWBX with dummy data and will post it here.

              If we can solve this then my very first Tableau project is ready for public .

               

              Martin

              • 4. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
                Martin Asztalos

                Hi Jim,

                 

                First of all - thanks so much for your support on my very basic questions.

                 

                Finally I made it to attach a TWBX file.

                In the "Data Source" sheet I created a calculated column called "Q3 Max per Filter" which contains the value for the top view range.

                 

                (I hope my formula synatx is correct)

                1. The formula in the calc. field "Q3 per SubGroup" determines the Q3 value for each box.

                2. The formula In the calc field "Q3 Max per Filter" determines then the max Q3 over the calc. field "Filter" and multiples by 2 --> which is then my top view range.

                 

                The aim is to have an automatic & dynamic top range for the y axis, so when going through each filter setting the y scale changes in respect to "Q3 Max per Filter".

                Do you think this is possible?

                 

                Please let me know if you need any additional information.

                Kind Regards

                Martin

                • 5. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
                  Jim Dehner

                  I have your file but I am at a loss for a solution (I will keep thinking but I don't know if I can come with one)

                  the issue I keep running into is that box plots by their nature use the dis aggregated data - any filter I try to apply needs an aggregated value -

                   

                  I wonder if there is another approach - but I am not sure I really understand the goal - could you explain what it is but in layman's terms - If the bottom line is you want the vis to look like something specific - then there may be other ways to get you there

                  Jim

                  • 6. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
                    Martin Asztalos

                    Hi Jim,

                     

                    Thanks for your answer.

                    I think I am the layman .

                     

                    I will try to explain my scenario but I would like to apologize for my English as I am not a mother tongue.

                     

                    The aim is to use a calculated field which is then used as the source for the upper range.

                    We will have many different views and adjusting the range for each view is quit old school and that's why an automatic solution would be awesome.

                     

                    Please let me know If you need additional information and have a nice weekend.

                    Martin

                     

                    Aim View:

                    For the first filter view my Y-axis has a scaling range from 0 to 4000.

                    For the second filter view my Y-axis has a scaling range from 0 to 400.

                    For the third filter view my Y-axis has a scaling range from 0 to 40.

                     

                    Fixed View (please see "Fix" sheet in TWBX)

                    A fixed view has the disadvantage not to see the full range of data like in "Filter View 1".

                    Even "Filter View 2 " has some boxes which are out of the upper range.

                    For "Filter View 3" the Y scaling is to broad and has the opportunity to zoom in for better visibility.

                     

                    Auto View (please see "Auto" sheet in TWBX)

                    The automatic view has the disadvantage that the upper range is defined by the outliers.

                    However, flagging those outliers are not an option as it would affect the mean calculation.

                    • 7. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
                      Jim Dehner

                      OK

                      Now I understand - what you want to do can't be done on a single sheet - but see the attached

                       

                      This is a way to get the end result on a dashboard - and it is a bit of a hack  I only did 3 of the parts numbers you would have to expand

                       

                      you will need a separate worksheet for each part

                       

                      on each sheet you will place the same show/hide filter but with a different value -

                       

                       

                       

                       

                      repeat for each sheet setting the filter to the single part number

                       

                      then create d/b

                       

                      Hide the sheet titles

                       

                      your d/b should now look like this

                       

                      Jim

                      Best I can do

                      If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                      • 8. Re: Dynamic upper y-axis range for a boxplot based on a LOD calculated field
                        Martin Asztalos

                        Good Morning Jim,

                         

                        Thanks for your patience and your detailed description.

                        In reality we have several hundreds of products and not every product is being made on a regular basis.

                        Making the sheets all by hand and add them to a dashboard / dashboards is quite an effort and needs maintenance.

                         

                        However, having a step back and try to have another look at the problem.

                        I found this article where I can select the Upper Bound manually using a reference line .

                        Dynamic Axis Range - Fixing One End (or both, or have it dynamic)

                        This works BUT only until the max data point of the view is reached and no further zooming in is possible .

                        Do you know if a Dynamic Axis Range using this reference line trick can be set lower then the max data point?

                        I have attached a new version of the TWBX and added a sheet called "Reference Line".

                        I would really appreciate for another help from you.

                         

                        Please let me know if you need additional information.

                        Martin