12 Replies Latest reply on Oct 18, 2013 11:04 AM by Jonathan Drummey

    changing bands based on parameter selection

    juliette.mccormack

      Hi everyone.

       

      I have attached a workbook which shows patient anxiety scores.  There are about 14 different questionnaires each having a different range of scores and each having different bandings.  I have written these out in the attached excel sheet.  The whole workbook, of which this is a part, is based around the user selecting the type of questionnaire they are interested in looking at (parameter [Select Score]). 

      My question is:  Can Tableau change the axis and bands for a chart depending upon the parameter selection?

       

      Currently, you can select the questionnaire and the data changes but not the axis or bands (choose GAD7SCORE and PHQ9_SCORE as others aren't v populated).  I get the impression that it is possible to do (http://community.tableau.com/thread/127229) at least in part, but I have only been using Tableau for a couple of months and the explanation is not detailed enough for me to understand completely.

       

      I suppose the other alternative is to make charts for each of the questionnaires and use a parameter on a dashboard to select the correct sheet but I was hoping to avoid this for neatness sake.

       

      Many thank for looking and hope to hear from you.

       

      Juliette

        • 1. Re: changing bands based on parameter selection
          Ramon Martinez

          Hi Juliette,

           

          The answer to your question is yes.

           

          I did a minor change the view GAD7Score Banding to reach what you want.

           

          First, I change the scale range of Y axis to Automatic, then I edited the band definition to Percentage of  AGG(Score), that is the metric you are using in Y axis, respect to the Maximum value.

           

          I didn't change the reference line, that you defined as constant to the value 7. But it is also possible to define it based on parameters.

           

          Check the attached edited workbook.

           

          I hope this help,

           

          Best,

          Ramon

          • 2. Re: changing bands based on parameter selection
            juliette.mccormack

            Hi Ramon

             

            Thanks v much for spending time on my question.  However, your workbook doesn't contain quite the answer I was looking for or perhaps I am missing something!

             

            Each of the questionnaires have different ranges and bandings (see the attached excel sheet from original message).  So, GAD7SCORE has a range of 0 - 21 and the bands are set at 5, 10 and 15.  However, PHQ9_SCORE has a range of 0 - 27 and the bands are set at 5, 10, 15 and 20.  GENANXSCORE has a range of 16-80, etc.  Leaving the axis as 'automatic' is not ideal as I would like to see all the bands and the full range of scores that the patient could have got.  Setting the bands at a percentage works for one questionnaire but then is incorrect for the others.

             

            Is there any way of adjusting the axis range and banding depending upon what questionnaire is selected from my parameter? @Jim Whal's  response to the thread mentioned above implies that it can be done in some way.

             

            Many thanks again for looking and for your time.

             

            Juliette

            • 3. Re: Re: changing bands based on parameter selection
              Ramon Martinez

              Hi Juliette,

               

              I was trying to figure out how to control the axis range based and bands based on parameters, but with no success so far.

               

              I don't know your specific requirement to do it that way, I mean showing one type of score per patient.

              I propose you a solution based on the idea that idea that we want to check the scores of all questionnaire per specific patient.

               

              See the attached workbook, worksheet Multiple sparklines per patient and the dashboard Score sparklines per patient. I simplify the scenario, just adding the scores of the two questionnaires GAD&SCORE and PHQ9_SCORE I didn't add the reference line for PHQ9_SCORE but you can add it at specific cut off value.

              Note that each one has their specific range in Y axis and bands.

               

              Let me know if this proposal works for you.

               

              Best,

              Ramon

              • 4. Re: changing bands based on parameter selection
                Jim Wahl

                Hi Juliette,

                 

                I expect Ramon is on to something (he usually is), but my initial thought on this problem was slightly different: Use blending to add the reference bands and ref line dynamically.

                 

                I used your IAPT Questionnaire spreadsheet as a secondary data source and added reference lines based on the measures Band 1, ...


                Here's what I did---there are a couple of minor changes I might make to clean it up, but I think you'll get the idea:


                Starting with your original TWBX file,

                1. Add the IAPT Questionnaire as a new data source.
                2. In the Data window, select the new data source. Click on the dimension Score > Rename to Score Name. We'll use this as the blending field and you already have a Score field in the original data source.
                3. Select the original data source. Create a new field Score Name = [Select Score] // The parameter.
                4. Duplicated the GAD7 worksheet.
                5. On the new worksheet, remove all of the reference lines.
                6. Select the new data source, make sure the link symbol next to Score Name is closed/orange. And move all of the reference measures (Band 1, 2, 3, 4, high, low, and Red Line) to the Detail button. Not absolutely necessary since there is only one value, but I would change the aggregation to MIN().
                7. Add new reference bands for each segment Lowest to Band 1; Band 1 to Band 2. When you add the reference lines, you'll now see the Band 1, .., measures as options in the Value drop-down menu.
                8. The one part I couldn't figure out is how to properly set the max Y-axis. (I'll take another look, but maybe someone else on the forums will chime in.) Instead, I used a Line and selected the Fill Above option.

                 

                You can also add the reference line this way. The tricky part is to add the reference line label. Since you can't use a dimension value such as Reference Line Desc, set the Label to None.

                 

                The workaround ("hack") is to add a second y-axis with this value and label the point. You can start by dragging the measure Ref Line to the right y-axis. Now you should have a dual axis. But try to click on the axis and select Synchronize Axes. You can't because there is a type mismatch between the left Score axis (which is an integer) and the right Ref Line axis (which is a float).

                 

                I also only want one value (the left-most). I created a calculated field Ref Line Label =

                // Required to synchronize dual axes
                // The data types on each axis must match
                IF FIRST() = 0 THEN
                    WINDOW_MIN(MIN(INT([Ref Line])))
                END
                
                

                 

                Drag this new field to the right axis and you should now be able to sync the axes.

                 

                Next, I changed the mark type for this new the second axis to Circle, and clicked Color > Transparency > 0%  to make the circle disappear. Finally drag Ref Line Desc to the Label button. Click on the Label > Alignment > Right.

                 

                I was a little rushed in typing this up. Let me know if it's confusing and I can walk through it or we could do a Skype screen share.

                 

                Jim

                 

                 

                2013-10-17 22-00-20.png

                • 5. Re: changing bands based on parameter selection
                  Jim Wahl

                  Also, just realized the Score line color changed to red after adding the dual axis. A neutral color like blue works much better, of course. ...

                  • 6. Re: Re: Re: changing bands based on parameter selection
                    Jonathan Drummey

                    The only way we can totally fix an axis to a specific range such as 0-21 and no more and no less is to use fixed axis sizes, which are manually entered. However, we can guarantee a spread by using reference lines, and where necessary making those reference lines invisible.

                     

                    I don't have time to set up a full example, here's how I'd go about this given the data you gave, which is a wide data set.

                     

                    - Create a data source using the band information you posted.

                    - Create a dimension in both sources that can be used to blend based on the parameter, in the attached I called it "Score Name".

                    - If necessary create calculations either the correct band information or Null depending on the measure chosen by the parameter. You'll need to use the MIN() aggregation to make sure to get the right values.

                    - In the secondary data source, click on the linking field to make it orange.

                    - Put all those calculations and measures on the Level of Detail Shelf of the view.

                    - Add the reference lines & bands, using those measures.

                     

                    I set this up in the attached, you can see that the highest score reference line changes based on the selected measure.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Re: Re: changing bands based on parameter selection
                      Jonathan Drummey

                      Nice work, Jim! @Juliette, Jim went into a lot more detail on how to set it up, the only extra bit from my post was that you can use that upper reference line to ensure the axis range is there and if you want, make it invisible.

                      • 8. Re: Re: Re: changing bands based on parameter selection
                        Jim Wahl

                        Thanks Jonathan. And thanks for reminding me of the invisible reference line trick. ...

                        • 9. Re: changing bands based on parameter selection
                          Ramon Martinez

                          Wow! That is excellent Jim.

                           

                          A very elaborated and flexible approach and solution. A lot to learn from a master.

                           

                          Best,

                          Ramon

                          • 10. Re: changing bands based on parameter selection
                            juliette.mccormack

                            Thanks v much everyone.

                             

                            Jim, I managed to follow your instructions all the way through.  I realise it takes a lot longer to write them out in such detail but it really does help.  Why did you change that second axis to a circle from line?  What does it affect?  My Ref Line Label has 30 null values.  Why doesn't yours?  Did you Filter Data, Show at Default Position or hide indicator?

                             

                            And then we get to the max y-axis issue.  I added the highest score reference line as Jonathan suggested but then noticed that if I added a band from Band 4 - Highest Score and added a label to the highest score I seemed to achieve the same results without the need for the highest score line.  I could leave the area above the highest score unfilled which defines the upper limit well.  What do you think?

                            Score Banding 2.jpg

                            It is great to have such experienced and knowledgeable input from you all.  This forum is my first experience of forums in general and it's such a great asset.  Most of my last couple of month's learning time has been looking at great threads on this forum (and the Tableau videos of course!).  Unlike MS products where you can ask any number of friends for an answer to a query, I know no-one who uses Tableau and my learning experience is very much dependent upon users (of all levels of experience) discussing, sharing and answering questions posted on the forum.  Your time and effort is very much appreciated.  Thank you.

                             

                            Juliette

                            • 11. Re: changing bands based on parameter selection
                              Jim Wahl

                              Hi Juliette,

                               

                              Thanks for the update. I like how you added a label to the upper reference line to force Tableau to show the entire range--hadn't seen that before. Cool.

                               

                              Regarding circles vs line for the secondary axis. You could use either. My initial thought was to use a circle so that I could make it as small as possible and hide it, but with the full transparency setting, I don't think it makes a difference.

                               

                              Have a great weekend.

                               

                              Jim