14 Replies Latest reply on Apr 6, 2017 8:57 AM by Joe Oppelt

    Dynamic change of sheets using a measure

    Lazy Rambler

      Hi All,


      I've been trying to find a way of dynamically changing the view based on the metrics.

      Please find attached the workbook.


      In the Dashboard 1, you can see that there are 4 metrics, say: Sales, profit, discount etc. And the sheets below are two different views: Say a bar chart and a trend. Is there a way, we can change the bottom two charts(Trend,bar), when one of the metrics is clicked?


      For example: If I click on the top left sales, my trend chart should be date vs sales, and bar chart should be segment vs sales. And if I select Profit, trend chart should be date vs profit, and bar chart should be segment vs profit and so on.


      I cannot use action filters as they can only be used on a dimension.


      Any ideas/thoughts ?

        • 1. Re: Dynamic change of sheets using a measure
          Joe Oppelt

          I have made dashboards that pop out different things based on a value selected, or on a sheet clicked.


          The attached (pop out value) workbook is a test example I used to prove out some ideas I had.  Dashboard 5 pops out stuff based on a value selected.  Dashboard 4 pops out stuff based on a sheet clicked.


          So I took the Dashboard-4 idea and applied it to your workbook.  See attached.


          Dashboard 1 in the attached copy of your workbook is your original dashboard.


          Dashboard 2 is the first step toward getting the pop-outs you want.  Notice that I created two new sheets:  Pop Out Sales and Pop Out Profit.  They are based on a new data source I added.  (See attached excel file.)  (If you have more than 4 different conditions, you'll need to add that many new rows to the excel file.)

          I also created two calcs in your original data source.  One has a value of 1, and one has a value of 2.  You'll need as many of these as there will be conditions to detect.


          Each pop out sheet is designed to contain one value in the dimension [Value].  (In my example, either a 1 or a 2.)  Each "Sum of ..." sheet has one of the new calcs, either the 1 or the 2.


          On dashboard 2 I added a floating container, and put a pop-out sheet and an associated Trend sheet.  I made the trend sheet a fixed width.  Now when you click on the Sum of Sales sheet, it passes the value 1 to the pop-out sheet, and that causes the sheet to display, pushing the actual trend sheet to the right.  Try it.  Now go to Dashboard 3.  I changed the horizontal container so that it starts way off to the left in negative space.  When the pop-out sheet is activated (click on Sum of Sales) it pushes the Trend sheet into view in the dashboard.

          This is called "popping".  It's a valuable technique that lets you conditionally display stuff based on any condition that you can capture logically.


          On Dashboard 4 I added a second container and did the same with Profit, only this time it activates with the value of 2.  Both Sum of Sales and Sum of Profit pass their initial value (1 or 2 respectively), and one or the other pop-out will activate, pushing the proper trend sheet into view.


          You can do the same with the second set of charts (segment).

          BTW, on these dashboards you'll notice that you get to see the "1" or "2" when the pop out is activated.  You don't really want the user seeing that.  In the respective pop-out sheets, click COLOR and select white.  Now the value will be white -- which is the same color as the background, and therefore essentially invisible.  Or, you can type in a larger negative number for the starting x-position to move that remnant of the exposed popout off the dashboard.

          1 of 1 people found this helpful
          • 2. Re: Dynamic change of sheets using a measure
            Madhura Dighe


            For that you have do few tweaking on your database; Dimensions should be in rows;


            If you can do this with to dataset; then below example will help you.


            Cheers! MD

            • 3. Re: Dynamic change of sheets using a measure
              Lazy Rambler

              Woah! Need to wrap my head around this. I did mimic it, and recreated the same dashboard using your technique, and it worked.


              I'm going to try this with my date set, and see how it works,. Thanks a lot, Joe


              One thing I'm trying to understand is the layout of the containers: For example: how did you get this container in dashboard2 to have three partitions?



              And also, I see that in dashboard 4, you have two containers and both of them look like they are overlaid, can you tell me how to do that, please.



              • 4. Re: Dynamic change of sheets using a measure
                Joe Oppelt

                First, the question about three partitions.


                In the attached I messed around with formatting on Dashboard 2.  (Also some of the sheets, so moving forward I want to go back to version "B" of this workbook.)


                The container on dashboard 2 always occupies 440 wide and 660 high, starting at x=3 and y=139.  (Click the LAYOUT tab and then click on the horizontal container in the list to see the specifics there.)



                Actually, I can answer your question about getting things to lay in precisely the same position here.  Just type in identical numbers for the overlaying object!  (And that's how you can change things to start on a negative coordinate.)


                SO on Dashboard 2 there are two objects in the container:  The pop-out sheet (which occupies just a few pixels when collapsed) and the actual data sheet.  The container occupies the full amount of real estate whether the objects are expanded or collapsed, and that third "partition" is just unused space within the container when things are collapsed.  (Consider it the dark matter of the universe.  )


                On Dashboard 2 I did borders on things.  I also colored the background of the container itself.  Notice that when things are collapsed, the container's border and color just wrap around the meaningful stuff.  But check this out:  I made a copy of this dashboard (see Dashboard 5) and I dragged an empty container into it.  (You can see that structure in LAYOUT for Dashboard 5.)  It expands to fill the rest of the empty space, and the grey color of the larger container now expands into that space.  (Mess with the Sum of Sales functionality on Dashboard 5 and you'll see that it doesn't behave exactly like Dashboard 2, but we could address that if we wanted to.  We're not inserting a container there in the actual solution, so it's not important here.)


                Anyway, that "dark matter" is the space where the data sheet will move into when the pop-out sheet expands.


                So you'll notice that the only real difference between Dashboard 2 and dashboard 5 is the starting x-coordinate, way off in negative space.  (Some of the others got changed marginally when I was messing around with things yesterday, but that's just incidental.)

                1 of 1 people found this helpful
                • 5. Re: Dynamic change of sheets using a measure
                  Joe Oppelt

                  I also want to point out something very important in Dashboard 4.


                  These two containers float on top of each other.  When you are messing with floating containers (for the record, FLOATING is the ONLY way I go when I do dashboards) tableau has an order regarding which is on top and what is below.  You can play with it in LAYOUT here  (right click on any object):



                  Or in LAYOUT, you can just drag the objects to rearrange order.  In DESKTOP, when you want to click on something (for instance to see tooltips) you can only click on the object that is on top of another.  Because these two containers occupy the same 2-dimensional space, you will only be able to click on the Profits sheet as it is currently laid out.  But when you publish this, the HTML treatment of these containers allows your users to click on the underlying sheets (if the topmost one is collapsed.)  So it can be a bit of a maintenance annoyance in Desktop, but it's perfect on Server.

                  1 of 1 people found this helpful
                  • 6. Re: Dynamic change of sheets using a measure
                    Lazy Rambler

                    Ah! This makes much more sense now. I see that I cannot interact with the slaes chart at all, so I'm assuming that any action filters from that sheet would not work ?

                    Is there a way to be able to interact with the sheet at the back, or this is how it is ?

                    • 7. Re: Dynamic change of sheets using a measure
                      Lazy Rambler

                      Makes much more sense now. Giving exact co ordinates. Still playing around with this to fully understand how layout etc work. Will hit back if I have any questions.


                      Thanks Joe.

                      • 8. Re: Dynamic change of sheets using a measure
                        Joe Oppelt

                        Lazy Rambler wrote:


                        ... I see that I cannot interact with the slaes chart at all, so I'm assuming that any action filters from that sheet would not work ?

                        Is there a way to be able to interact with the sheet at the back, or this is how it is ?

                        Are you clear on this question?  In my previous reply I pointed out that for the user, this works just fine when published to server.  It's just an annoyance for you to work with in Desktop.


                        (Unless you don't use server for your users.  Then this becomes a different problem.)


                        When I am working with this issue in Desktop, I temporarily move the upper container out of the way by starting it -1000 further out into negative space.  (I just pick 1000 because it's easy to remember what it should be when it's time to move it back.)

                        • 9. Re: Dynamic change of sheets using a measure
                          Lazy Rambler

                          Hi Joe,


                          Apologies to ask again, I'm trying to apply the same logic to my dashboard but cannot get it to work. Could you please help me out


                          I have 8 metrics, where the same login has to be applied. I've been trying on 2 metrics, with the instructions you have mentioned, but I guess I'm not able to do it


                          In the attached workbook, in the metric selection dashboard, I'm trying to get this working for pipeline and revenue. I created two containers(floating) and gave them the negative x dimension. I also have like an excel sheet with the 1,2,3,4 listed, and I created the pop up sheets, normal trend sheets, and applied the filters, I'm wondering what I'm doing wrong. Joe Oppelt

                          • 10. Re: Dynamic change of sheets using a measure
                            Joe Oppelt

                            On your dashboard I pulled the container for revenue pop out into positive space.


                            The pop-out sheet is working, but it has a very narrow fixed width.


                            You can tell it is FIXED by the little pin on the sheet's border:  (Red circle).



                            Usually you can un-FIX it by clicking on that pin, but the sheet is so small that the cursor picks up the drag-point to drag the sheet around.  You can also uncheck it by right-clicking on the sheer in the LAYOUT tab and de-selecting "Fixed Width".  (Circled in purple.)


                            So I did that on Dashboard 2.  Also on Dashboard 2, I edited action "Filter 1 2" to "Exclude all values" for "Clearing the selection".  What this does is change the Source Field (set to 3) to null so that nothing is getting passed to the pop-out sheet, and therefore nothing will display and the pop-out sheet collapses back to hide the Revenue Trend Sheet.


                            I think you get the fixed-width pin because you tried to drag-resize the pop-out sheet size.  Once you drag the width, Tableau interprets that as a request to fix the width.  The way to extend or shrink it is to drag the width of the text mark on the sheet itself.  To do that, remove the action pill that the filter action has inserted on the sheet:



                            Now your mark will be displayed and you can drag-stretch (or shrink) it as needed.  Tableau will resize the sheet in the dashboard container to accommodate the new size.  The next time you click the source sheet on the dashboard, Tableau will re-insert the action filter pill on the pop-out sheet.


                            Yes, this can get tedious.  But once you've done this a few times, manipulating the sheets and the container becomes more straightforward.

                            And yes, when you are initially setting up your container to do what you want, do it all with the container in positive space like I have on Dashboard 2.  Once you are happy with it, reset the x-position to -450 (or whatever) and that will be that.

                            • 11. Re: Dynamic change of sheets using a measure
                              Lazy Rambler

                              Thanks Joe. In the dashboard 2, I see that when I select pipeline I do not get the Pipeline trend. But I see that in the actions, the pipeline trend is selected. So, is there something specific I have to do to get the pipeline too ?

                              • 12. Re: Dynamic change of sheets using a measure
                                Joe Oppelt

                                Yup.   I only messed with one of them.  (Revenue.)  You'll need to replicate the same thing for the others.

                                • 13. Re: Dynamic change of sheets using a measure
                                  Lazy Rambler

                                  Thanks Joe. I have finally started to understand this, and got it working for 3 metrics.


                                  If I have to have a second set of charts(say a bar charts), should I be creating 4 new containers with them, or should I drop the new charts into the exisitng containers ? What would you think would be better ?


                                  I tried putting them in the same containers, but cannot get them to work. Joe Oppelt

                                  • 14. Re: Dynamic change of sheets using a measure
                                    Joe Oppelt

                                    If the bar charts are governed by the same logic as the line charts, you could do line-chart and bar-chart with one pop-out.  You will have to change the pop-out sheet to be as big as the two data charts combined, of course.


                                    If you have only one pop-out mechanism per measure, you'll have less maintenance because you'll have only one "push" sheet to maintain.

                                    But there's nothing wrong with doing it in two separate pop-out "machines" either.


                                    BTW, you could actually have the two data sheets positioned in one container, and then drag that whole container into the pop-out container.  See Dashboard 2 for an example.  In there, I have the data sheet plus a bogus sheet in a container, and then I dragged that container into a container with some text boxes, and then I dragged the enclosing container into the pop-out container so that the whole unit pops out when you click on REVENUE.  One of those text boxes could say, "This is REVENUE stuff..."  This is limited only by your creativity!