7 Replies Latest reply on Oct 13, 2016 7:13 AM by Alvin Tarrell

    Synching axes from multiple sources on one dashboard?

    Alvin Tarrell

      Not sure I am doing this right, but I am trying to build a dashboard to display blood pressure readings synched up with a patient's prescription history.

       

      I have the data on a single spreadsheet (BP readings), but I could not figure out how to use the prescription portion of that, so I created a separate tab (Scripts) for that data.

       

      The attached example shows a simple line graph for the blood pressure readings on top and a Gantt-style chart for prescriptions down below.  I would like to do two things with those two elements:

       

           1.  Synch up their x axes (so that viewing them together makes sense), and

       

           2.  Make them both respond to the date-time filter shown.

       

      Not sure I can do that now that I have two separate data sources . . .  please help with suggestions on how to make this work.

       

      Thanks!

       

      Alvin

        • 1. Re: Synching axes from multiple sources on one dashboard?
          Joe Oppelt

          I'm looking at this.


          What are you trying to synchronize here?  What measure from BP Readings do you want to display with what-other-measure from Scripts?

           

          And what axis are you looking to use along the X-Axis?

           

          I can tell you this: You will not get dates from different Data Sources to obey one filter.  Not in 9.3 anyway.  That capability is in 10.0 though.  If you cannot upgrade to 10, then you need to control the dates by using parameters.  Create a START DATE parameter and an END DATE PARAMETER, and then create a calc that looks like this:

           

          [Date-Time] >= [Start Date parameter] AND [Date-Time] <= [End Date Parameter]

           

          Put that on the filter shelf and select for TRUE.  Do the same with your Date field from the other data source.

          • 2. Re: Synching axes from multiple sources on one dashboard?
            Alvin Tarrell

            Thanks Joe.  Basically I am just trying to synch up the x axis (date-time) on the two worksheets as shown on the dashboard.  For example, adding a prescription should reduce the blood pressures - that should be easy to see if the time axes line up, but is much harder if they don't.

             

            I am hopeful I can get this to work from a single data source, but if not it sounds like upgrading to 10.0 is an option.  We're  working on that now, actually, but it may take a while.

             

            Thanks again!

            • 3. Re: Synching axes from multiple sources on one dashboard?
              Alvin Tarrell

              Thanks for the parameter tip too - forgot to mention that!

              • 4. Re: Synching axes from multiple sources on one dashboard?
                Joe Oppelt

                You kind of have yourself in a quandary.  I was asking about the measure you wanted to sync because you can actually bring measure values from the secondary source and add reference lines on the chart from your primary source.

                 

                Let me step back a bit.  You can force your axis to extend beyond its minimum/maximum as determined by the data.  You can add a reference line that is a value beyond the data line, and tableau will extend the axis to accommodate the reference line.

                 

                So, for example, in the attached I have change the BP plots to remove the zero point.  (Right click the blood pressure readings axis, and uncheck the "remove zero" box in the upper right corner of the edit screen.)    Sheet 4 shows what that looks like.  Then I created a calc to get data from the secondary source.  (Notice that I didn't select any blend field.  This will force tableau to look at the whole data set.)  See this calc:  [Duration in Days from Script].  I used that to create a reference line in sheet 3.  See Sheet 3.  See the difference?  Tableau changed the axis to accommodate the reference line, and I created that reference line from the secondary's data.

                 

                The problem you face is that you are looking to impact your DIMENSION axis with dimension data from the secondary, and the only way to get secondary data is via aggregate, which makes it a measure.  And you can't use a measure for a reference line on a dimension axis.

                 

                You can see that I *CAN* find the min and max date value from the secondary source.  See [Max date from Scripts], for example.  But I don't have it available to me to set a reference line.  I can even do calc math on that calc, comparing max from Scripts to max from BP, and doing a MAX to get the difference.  (That's what you see in the title.)  I was hoping to be able to use [max date for sheet] as the reference line (and do the same in reverse on the Scripts sheet) and force a max reference line on each sheet to force a uniform start and end.  But because [Max from Scripts] has to be treated as a measure, so will [max for sheet], and I can't use it on the date axis. 

                 

                I have forced separate bar graphs to have the same measure axis range this way, but this was the first time I tried it for a dimension, and it's disappointing, but I can understand why it is working this way.

                 

                BTW, after you add the reference line, you can edit it to have no visible line and no label, so the user never sees it.

                 

                Now let me suggest this:  If you use the parameter method to set your date ranges as a filter, you CAN use the parameter values also to set the dimension reference lines on your sheets.  See Sheet 5.  I created a date parameter set to a value in the future.  I used it to add a reference line.  And in Sheet 6 I edited it to display nothing, but it's still there on the sheet.  Something else to consider.

                • 5. Re: Synching axes from multiple sources on one dashboard?
                  Alvin Tarrell

                  Thanks again Joe.  Do you see any way to use the prescription data from the primary source?  (BP readings)

                  • 6. Re: Synching axes from multiple sources on one dashboard?
                    Joe Oppelt

                    Well, you sort of can.


                    You have to run the data along some common value, so I blended on dates and added SUM([scripts].[Duration]) to the same sheet.  Problem is, to make it dual axis we can have only two measures, so I changed the way we are displaying the BP stuff to use the measure-names/measure-values method to show the two measures from BP.  Then I could add [Duration] from SCRIPTS.  (See the original BP sheet where I did this.)


                    The way your data is, your options are very limited in what you can do with it.  But here is one thing you can do.

                    • 7. Re: Synching axes from multiple sources on one dashboard?
                      Alvin Tarrell

                      Thanks Joe.  I think I'll either see about upgrading to version 10 and/or just manually coding the prescription changes as reference lines on the blood pressure chart - that seems like the best way to achieve what I am really trying to show.

                       

                      Best,

                       

                      Al