1 2 Previous Next 19 Replies Latest reply on Jan 24, 2013 11:30 AM by Jackie Klein

    Action filter with data blending

    Jackie Klein

      I have a dashboard that contains 3 worksheets (see attached example).  This is just a sample that shows it by state, but I am actually trying to do this by Employee.  I am trying to get it so that when you click on a State on the Dashboard, both of the graphs update, however only the Sales graph is working.  I assume this has to do with data blending.  I am looking for a fix that will be dynamic as Employee names can potentially change daily (one major difference over States).  I just used States as the example here as this is the sample data I had readily access to. 

       

      Thanks in advance!
      Jackie

        • 1. Re: Action filter with data blending
          Catherine Rivier

          Actually, this is a really simple issue: you don't have both sheets checked as your Target Sheets in your Action description.  Source Sheet is Sheet 1, and the Target Sheet are both Graph and Sheet 2:

           

          fix.bmp

          • 2. Re: Action filter with data blending
            Jackie Klein

            Well, that doesn't appear to work.  I do not want Sheet2 to update with anything, I just want the Graph to update when I select a state on Sheet 1.  For example, Indiana has total of $466k in Sales and $22k in Profit.  I want the graph to reflect this when I click on Indiana, but only the Sales is updating on the Graph.  Profit still shows in Total (all states combined).

            • 3. Re: Action filter with data blending
              Catherine Rivier

              I'm sorry about that - I misread your initial issue.

              The reason it's not picking up is because in your Graph visualization, the Primary and Secondary data sources are connected only by Date.  (When you look at the Secondary data source, it's the only field with a link symbol.)  So when you change the State through the action, it does nothing to the Secondary data source.

               

              A field can only be linked if the field from the Primary is somewhere in the view.

              (Side note:  This is a huge pain, but from what I understand it is going away as of Tableau 8.  In 8, I believe you no longer need to have the field present in the Primary to be able to link to the Secondary, which is awesome...)

              So in your Graph view, drag State to the Level of Detail, and your action filter will work correctly.

               

              The only issue you'll quickly see is that not selecting ANY states makes it so each state has one line, which I'm sure isn't the view you want to see.  So I did just create a new issue with that fix...

              I know of 4 ways to deal with it:

              1. Turn the view from a line graph into a stacked Bar chart, or Area chart.  It will still behave the same way but it will look like one continuous bar chart.
              2. Manipulate your data (union queries are great for these) so you replicate your data for all states under State name "All States".  This has plenty of problems though....
              3. Separate the 2 graphs (Profit - Secondary and Sales - Primary) and just have them as different visualizations.  Avoid the whole mess altogether.
              4. Try out a version of Tableau 8 Beta and build your graph there (which should cut out all of these problems)
              • 4. Re: Action filter with data blending
                Jackie Klein

                Ok, so the only thing I have to do is drag State to Level of Detail on my Graph tab?  I tried that and went back to the dashboard, and when I click on Indiana, the Profit doesn't show anything. 

                • 5. Re: Action filter with data blending
                  Catherine Rivier

                  Was working on a sample workbook

                   

                  Only about 20 states have any Profit in the Coffee Chain data, so most of them will show up as blank. (See tab "Data - Profit".)

                   

                  Here's a sample with a few of the solutions I talked about.  Graph is your original view, which will work for every state, but will look bizarre when no states, or multiple states, are selected.  Graph2 shows that changing this to a bar graph hides this issue - and Graph 3 shows the same in an area chart.

                   

                  Then on the right side, I show them separated out so you don't have to deal with Primary/Secondary in any view.  They are just two unique views I stacked visually on the dashboard.  (Note with this one you have to update your Action filter under Target Filters.  Right now you only have State from the Access data source linked to itself.  You also need to add a new Target Filter that State from the Access data source is linked to State from the Excel data source.  If you edit the Action filter, you can see this more clearly than how I'm describing it...)

                   

                  Does this work for you?

                  • 6. Re: Action filter with data blending
                    Jackie Klein

                    Well I really wanted Sales and Profit on a dual access on one graph, and wanted it to show the total sales if no state is selected instead of 48 lines on the graph.  Is there a way to do that? 

                    • 7. Re: Action filter with data blending
                      Catherine Rivier

                      I don't think (in Tableau 7) there's a way to build this as a line graph, unless you try Tableau 8 Beta or modify your data source.  But I'm a fan of either the Bar or Area chart for this, or even a combination of both.  See the attached view (Dashboard 2 - Dual shows all of the options) and see what might work best for your situation.

                       

                      For the Area chart you can play with the colors and the transparency to get it to look right for what you need.

                       

                      FYI, the reason these are the only ones that work is because you need a visualization that will allow you to stack marks.  This all comes with using Primary/Secondary data sources in a single view, unfortunately...

                      • 8. Re: Action filter with data blending
                        Jonathan Drummey

                        Table calculations can do this. One thing to consider in Tableau is that if you want to re-aggregate across dimensions in the view, either you're going to need to hack some SQL, do some funky blending, or use table calcs. Given the filtering using Action Filters and the goal, table calcs made the most sense to me.

                         

                        Here's what I did:

                         

                        Set up a Count of States measure to get the total number of states. This use a table calc to identify the total number of states, with Compute Using of Order Date & State

                         

                        Set up a Sum Sales Revised measure that returns the total sales if the Count of States is 48, else just the Sum of Sales. This has Compute Using of State, with nested Compute Using for Count of States.

                         

                        Set up a Sum Profit Revised measure that is doing the same thing with Profit from Coffee Chain.

                         

                        Set up a State Revised measure that uses the same statement to return "All" if we're returning all the states and the state for the given state. This goes on the Level of Detail, and then we can turn off the tooltip for State. This is because we're putting the total into the value of the first state on the list (Alabama), and we don't want to show a huge value for Alabama in the tooltip when it really should be "All". This calc also has a compute using of State, with the nested compute using for the Count of States.

                         

                        Something that I don't like about the line chart is that it gets really busy when a lot of states are selected. Something I might do is set up a parameter to turn on/off a field based on State Revised and then put that in the label for the first or last (probably last) mark, so users could turn on/off labels.

                         

                        See the Dashboard 3 - Dual and Sheet 14 in the attached for details.

                         

                        Jonathan

                        • 9. Re: Action filter with data blending
                          Jonathan Drummey

                          FYI, Catherine, you can stack marks (and unstack marks) for any worksheet using Analysis->Stack Marks. Area charts with unstacked marks can make for interesting comparisons with the right data.

                          • 10. Re: Action filter with data blending
                            Catherine Rivier

                            Jonathan, for some reason your example workbook has an error in the view with Measure Names when I open it - and when I replace them as new, it's still showing up with multiple lines?  But I'd love to see your alternate solution...

                             

                            Oh, and turning on Stack Marks was how I got the view to show up in the first place, so I'm well aware

                            • 11. Re: Action filter with data blending
                              Jonathan Drummey

                              Hm, I opened it again from my machine and it works fine, I'm running 7.0.12. Can you try downloading it again?

                               

                              I made the point about stacking marks because you'd written: "FYI, the reason these are the only ones that work is because you need a visualization that will allow you to stack marks.  This all comes with using Primary/Secondary data sources in a single view, unfortunately..." Since we can stack and unstack any mark type, I'm not understanding the first sentence?

                              • 12. Re: Action filter with data blending
                                Jonathan Drummey

                                Whoops, didn't mention that I'd actually re-uploaded the file just in case there was something broken in that process.

                                • 13. Re: Action filter with data blending
                                  Catherine Rivier

                                  I downloaded again, I'm still seeing the error...  I am using 7.0.1 (cannot update regularly due to security, which is why I'm on an early 7).

                                  fix.bmp

                                  I'm guessing this is some kind of version issue.  I tried it on my personal (student) version, which is 7.0.5 and the sheet shows up without error.  However, I'm still seeing multiple lines on the chart with multiple selections.  This is what I'm seeing - this isn't what you were intending, was it?

                                  Capture.PNG

                                   

                                  I made the point about stacking marks because you'd written: "FYI, the reason these are the only ones that work is because you need a visualization that will allow you to stack marks.  This all comes with using Primary/Secondary data sources in a single view, unfortunately..." Since we can stack and unstack any mark type, I'm not understanding the first sentence?

                                  Actually, you can't stack Lines (if you try to turn on Stacking, it switches it to an area chart), for example.  To have the view we're looking for work, you need to a) stack the lines, and b) have them seem to blend into one, so it seems like it's just a view of the total sum.  You could do so if you weren't breaking it up by State, of course, but because data linking by State requires you to break it up by State (via Level of Detail), this creates this unique restriction of not being able to show a single line graph representing all States.  That's what I was - perhaps clumsily - trying to get across....

                                  • 14. Re: Action filter with data blending
                                    Jonathan Drummey

                                    Versioning: There were a couple of changes to table calcs in the 7.0.x series up to 7.0.5 that changed how table calcs work, one of them involved how table calcs were sorted (I think that one was in 7.0.2). I'm not sure how I'd do this workbook in the earlier 7.0.x. If you're going to do much with table calcs, I really recommend 7.0.5 or higher, I used Tableau Student 7.0.0 at home up until a couple of weeks ago and it got really painful at times.

                                     

                                    Single/multiple lines: In terms of multiple lines for multiple states, I was going off of this statement by Jackie: "Well I really wanted Sales and Profit on a dual access on one graph, and wanted it to show the total sales if no state is selected instead of 48 lines on the graph.  Is there a way to do that?" I assumed that if there were a few states selected, then the each state should be drawn. However, if the goal is just one, that's even easier than the previous view I'd set up. All that's needed is a calc with the following formula: IF FIRST()==0 THEN TOTAL(SUM([Sales]) END. The TOTAL(SUM([Sales])) gets a total value of sales, the IF FIRST()==0 just returns that for only one State in the data. I set this up in Sheet 15 in the attached.

                                     

                                    Mark Stacking: Yes, if we turn Stack Marks on for a Line chart Tableau will convert it into an area chart. However, all we have to do then is change the Mark Type back to Line and we'll have stacked line marks. I set that up in the "stacked line" sheet in the attached.

                                     

                                    Jonathan

                                    1 2 Previous Next