10 Replies Latest reply on Dec 4, 2018 3:33 PM by Joe Oppelt

    Ability to Automate Dynamic Sheets on Dashboard?

    David Mouser

      Hello,

      I have successfully setup a test dashboard with a parameter to affect filters and toggle between two sheets, per the following:

      Create a Sheet Selector for a Dashboard - Tableau

       

      Is it possible to have the sheets automatically display on the dashboard dynamically without any user input to affect a parameter?

       

      I would like for the toggle to be automatic based on a value or combination of values from another sheet/graph on the dashboard (all in the same data source).

       

      I have the following graphs on the test dashboard:

      • Breakdown of Activities by % of hours
      • Top 10 Activities in Category X
      • Top 10 Activities in Category Y

       

      The challenge is some personnel work Category X, some Y and some both. If a user of the dashboard navigates down to a level (office or person) where one of the Activity values is 0, then there's currently a large blank where the corresponding Top 10 sheet is on the dashboard. I would like this to dynamically display either or both depending on what activities are being reflected on the Activities by % of hours graph.

       

      Due to the nature of the data, I cannot share. If I have time later today, I'll see if I can come up with a dummy dataset to post with my example above.

       

      I'm thinking this may be possible by way of a calculated field to affect the parameter/filter or an Action Filter, but I'm not sure. Any advice would be appreciated.

        • 1. Re: Ability to Automate Dynamic Sheets on Dashboard?
          Joe Oppelt

          Yes you can.

           

          Just like you are currently using a calc to evaluate the value of the parameter, your calc can evaluate the value of a field or of what's un the underlying table.

           

          You can do this two ways.  If you can re-generate the value from your first sheet in the two that you are planning to swap, then you have it right there in the sheet and you can use the calc right in your filter on the two swap sheets.

           

          Otherwise you can "slide" the two sheets in a wide (or tall) container so that the one you want displayed is pushed into a viewing area.

           

          In the link below are a series of videos about sheet swapping and sheet popping.  The second method I just described is demonstrated in the third video at this link:

           

          New series of videos on swapping and popping on a dashboard

          • 2. Re: Ability to Automate Dynamic Sheets on Dashboard?
            Joe Oppelt

            If you struggle with implementing this, I can help you.  Either upload your packaged workbook, or upload a superstore example that you've hacked up to simulate what you are trying to do.

            • 3. Re: Ability to Automate Dynamic Sheets on Dashboard?
              David Mouser

              Joe Oppelt thank you for the replies. I reviewed the posts and some of the videos, but I'm not quite getting there.

               

              I've attached an example via Superstore that is similar to what I'm trying to accomplish.

               

              As you can, I setup two Top 10s split out by shipping methods, which can be toggled by the Shipping Methods parameter selector.

               

              If you look at the Reference Orders tab, you'll notice Pauline Johnson has two orders, for "Same Day", which both fall under the Top 10 Other.

               

              If you select her from the Customer Name drop down the Shipping Mode Distribution table on the left reflects to only show "Same Day", so "Fist Class" is no longer displayed.

               

              With First Class not displayed, how can I automatically force the parameter selector to "Other", thus making the Top 10 First Class table not be displayed?

              • 4. Re: Ability to Automate Dynamic Sheets on Dashboard?
                Joe Oppelt

                Something that really helps with swapping sheets and popping out objects is to float your objects.  On your original dashboard I floated the SHipping mode sheet and the top-10 First sheet.  (I used BLANK objects to fill in the tiled spaces so that everything else stayed in their original positions.)


                Go to copy 2 of the dashboard.  Here I floated a blank under the shipping mode sheet.  And I colored it so you can see it.  You will probably not want it colored, so select a color of white so that it still has a color.  (Color makes the blank opaque.  I need a solid object to hide things behind later on.)

                 

                Next I made a copy of your Shipping mode sheet and named it "Pop out sheet to display first class".  Take a look at that sheet.  There is a calc [Calculation1] that I use to determine of "First Class" is on the sheet.  It adds up the number of rows where "Firtst Class" occurs.  (The way the sheet is laid out, the number will be either 1 or 0 because there is only one first class row, as it turns out.)  I display that in the title of the sheet.


                Now go to copy 2 of that sheet.  I put that calc on filters.  If the value is at least 1, then display all the rows.  Otherwise display no rows.  I also stretched the sheet to be wider than the Top 10 First sheet.  Play with the customer name filter and select Pauline.  You'll see the sheet disappear if she gets selected.  (Note:  As you already have it set up, you need that customer name filter applied to this sheet as well as the others.)

                 

                Go to Dashboard copy 3.

                 

                I made a giant-long container into which I dragged the second copy of the pop-out sheet, and then dragged to top-10 First sheet behind it.  I did "Hide Title" for the pop out sheet.  (This is important with swapping and popping.  If you don't hide that title, the title remains even if the sheet is blank.  You can see that when you are editing that sheet and playing with the customer name filter.)  I also made note of the width of the top-10First sheet before I dragged it into this container (1385) , and I set fixed width on this sheet to 1385 so that it stays that size no matter what the pop-out sheet does.  (Otherwise, when the pop-out sheet disappears, the top-10 sheet will expand to fill the container.)  Right now that container is centered on the dashboard.  Play with the filter and see what top-10 does.  It moves to the left or to the right based on the existence of "First Class" in the pop-out sheet.

                 

                Go to Dashboard copy 4.  Here I positioned the container (with ALL Customers selected) so that it lands where I want to see it.  Play with the customer filter and see what happens.  A secret to this method is that we can position floating objects to start in negative coordinates.  This

                 

                Finally, go to dashboard 5.  Here I changed the floating order of things so that my horizontal container floats below the tinted blank container.    This container landed at a starting location of negative 1151.  So a lot of the pop out sheet is outside the boundaries of the dashboard perimeter, and therefore out of view.  The rest of it is behind the opaque tinted container.  And when you select Pauline, the Top-10 sheet shifts into that hidden space.

                 

                This is the essence of object popping.  In the video I was popping out various filters for the selected sheets into a viewing area using the same principles.

                 

                Take some time to digest this.

                 

                Then I want to discuss what you are really trying to do with these two top-10 sheets.  If you actually filter out customers like this, your top-10 logic will only operated on the selected customer. and you'll get only the top-10 rows for that customer only.

                • 5. Re: Ability to Automate Dynamic Sheets on Dashboard?
                  David Mouser

                  Joe, thank you. I see what you outlined aligns to the videos you posted. I follow the concept and steps you discussed, including the pop-out and hiding.

                   

                  I am trying to get to the point where an individual is selected, and the dashboard reflects the applicable data in each Top 10 bucket. I originally combined for an aggregate Top 10 which would automatically adjust content, but was asked to break them into two separate buckets. Recognizing people may fall into one or both buckets, I'm trying to figure out a way to dynamically adjust the dashboard so that when somebody doesn't have anything in one of the buckets that area on the dashboard in a blank space. Alternative, if they do have content in each bucket, I still want each to display.

                   

                  I think swapping may be sufficient since I can hide one of the Top 10s if criteria isn't met and the title is hidden. The main piece, is I want this to be done automatically based on values without a user selecting a parameter dropdown. Essentially:

                   

                  If Shipping Method:

                  = First Class + Same Day, Second or Standard; Parameter = All

                  = Just First Class; Parameter = First

                  = Just Same Day, Second, or Standard; Parameter = Other

                  • 6. Re: Ability to Automate Dynamic Sheets on Dashboard?
                    Joe Oppelt

                    David Mouser wrote:

                     

                    ...

                     

                    I am trying to get to the point where an individual is selected, and the dashboard reflects the applicable data in each Top 10 bucket. ...

                    The Customer Name filter isn't doing that though.  When you select a  single customer, all the top-10 stuff is re-calc'd just for the top-10 rows for that customer.

                     

                    Make it a parameter.  (And starting in 2018.3 you can do a "set action" that mimics a parameter, and it will get auto-updated as your data changes, so no more manual maintenance of parameter lists!)

                     

                    Then use a table calc to filter out the other people from the two vizzes.  This will leave your underlying table intact, and top-10 will always be top-10 of the whole business, not just the top-10 rows for the selected person.  And then you'll display the pertinent info for that person from the original top-10 set.

                    • 7. Re: Ability to Automate Dynamic Sheets on Dashboard?
                      Joe Oppelt

                      David Mouser wrote:

                       

                      ...

                       

                      The main piece, is I want this to be done automatically based on values without a user selecting a parameter dropdown. Essentially:

                       

                       

                      You'll notice that the stuff I did had no effect on the parameter chosen (for First vs Other).  You don't really need that if you just want to control the two sheets off the actual data collected.

                       

                      One note:  If you do a table calc filter on the display of the top-10 stuff for the selected customer, since the whole table remains intact, the LOD I used will need to be modified to SUM only if ALL is not chosen and the customer = the parameter.  But the overall principle remains, that you can control the display of the sheets based on the data vis-a-vis the parameter selected.

                      • 8. Re: Ability to Automate Dynamic Sheets on Dashboard?
                        David Mouser

                        Joe, I think you lost me.

                         

                        If you look at Lindsay Williams (San Fran) in Superstore, she has 4 orders. They are all Standard shipping, so the results display in the Top 10 Other Shipping table, while the Top 10 First Class is empty.

                         

                        Since the Shipping Mode Distribution only reflects the 4 orders at Standard Shipping, how can I have that automatically affect and set the parameter to Other, and in turn hide the Top 10 First table?

                        • 9. Re: Ability to Automate Dynamic Sheets on Dashboard?
                          Joe Oppelt

                          Here's what I was saying.

                           

                          Lindsay's orders aren't part of the "All" top-10.  In the attached I added customer name to the sheets.  She's not on there.  (I changed City and State filters to pertain to all sheets to make the Lindsay testing easy.)

                           

                          Maybe I'm misunderstanding, but it's my impression that you want to show a customer's info as it pertains to the overall top-10 stuff.  If not, then what you are doing now is fine.  You'll show up to 10 orders for the selected customer, or else the top-10 among all customers if you select ALL.

                          • 10. Re: Ability to Automate Dynamic Sheets on Dashboard?
                            Joe Oppelt

                            And what I'm doing on dashboard 5 already takes care of what you are looking to do.  The "First" sheet goes away for Lindsay.  And it has nothing to do with the shipping methods parameter.  In fact, I took that off Dashboard 5.  You don't need it if you do it the way I'm doing in here.  (You'll need to do the same sort of thing for the "Top-10 Other" sheet as well.  Pop it out of view if there are no rows there.)

                             

                            For the record, there is no way to change the value of a parameter dynamically.