7 Replies Latest reply on Jun 25, 2013 10:43 AM by Stuardo Herrera

    Working without aggregations

    Stuardo Herrera

      I am working on the following dashboard, that is based on a dual axis map:

       

      http://public.tableausoftware.com/views/Maptest_1/Dashboard?:embed=y&:display_count=no

       

      When the user clicks on a certain City, the line chart automatically updates showing the index performance for the selected City. When the user clicks outside any city, on the State layer, the line chart goes blank. I need that the line chart shows the performance of the State. Normally, you would do this through aggregations, but in this case, the index for all the cities of a State cannot be calculated through aggregations. I have added a record for each State in my data set in which the City is called "ALL"  and contains the index for the whole State.

       

      How I can do that when a user clicks in the state, the line graph shows the index for all cities of the record that already has this data (the one with the city as "ALL").

       

      Thanks in advance!

       

      -Stuardo

        • 1. Re: Working without aggregations
          Jonathan Drummey

          Hi Stuardo,

           

          See the attached. What was happening was that when the state gets clicked on, the Action Filter is sending Null for a City value to the City Index worksheet. I added a calculated field that returns Null for the ALL city, then adjusted the Filter Action fields so it uses that field in the City Index target worksheet instead of the City field.

           

          I'm not sure what you mean by, "Normally, you would do this through aggregations, but in this case, the index for all the cities of a State cannot be calculated through aggregations." There are relatively few cases of aggregation that cannot be computed within Tableau (often using table calculations to get aggregations of aggregations), can you describe more of the issue that you ran into?

           

          Jonathan

          • 2. Re: Working without aggregations
            Stuardo Herrera

            Thank you for your help Jonathan. Could you please publish it on Tableau Public? For some reason I can only open the previous version.

             

            The thing is that I only have the index for each city and for the whole state, not the information from which those indexes were calculated, and to calculate the index for the state as a whole I cannot simply sum or average (or other type of aggregation) all the city indexes. I would need to have the raw data.

            • 3. Re: Working without aggregations
              Jonathan Drummey

              Thanks for the explanation, that makes sense. Here's the Tableau Public link: http://public.tableausoftware.com/views/MapFilterActionwithExistingAgg/Dashboard?:embed=y&:display_count=no

              1 of 1 people found this helpful
              • 4. Re: Working without aggregations
                Stuardo Herrera

                Your solution totally worked out for my dashboard Jonathan!

                 

                Thanks a lot!

                 

                -Stuardo

                • 5. Re: Working without aggregations
                  Stuardo Herrera

                  Sorry to bother you again Jonathan Drummey , but on this same example, I have added a "State" filter. When I select a new State on this filter, the line chart still shows the last selected city. I want it to show the "ALL" value when the user selects a State. I have been playing with the logic of the dashboard but I can't get to pull it off.

                   

                  Workbook: Map Filter Action with Existing Agg

                   

                  Any help is apprecciated.

                   

                  -Stuardo

                  • 6. Re: Working without aggregations
                    Jonathan Drummey

                    What's happening is that the Filter Action and State filters are crossing each other up:

                     

                    - Let's say that Ithaca, NY has been selected, so that's the value for the Filter Action.

                    - Then when the user chooses Connecticut from the Quick Filter, that's the value for the Quick Filter.

                    - The City Index field is then trying to get Ithaca, NY AND Connecticut and of course there's no state that is both NY and CT.

                     

                    There's no way I know of for a Filter Action to change what is *currently* selected in a Quick Filter, or vice versa (though once we choose one then that can change what options are available for the other). The same goes for parameters, which can sometimes be used in these situations. I'm not sure of what your goal is for the interactions in this case, and that makes it harder for me to come up with any ideas. One thought I have is to get rid of the Quick Filter, show all the states at once and use Highlight Actions, so all states/cities would be shown at once and the user could click on a specific state to see results for that state, or click on a city to see results for that city. That would certainly require some explanatory text, though. I'm sorry I can't come up with more options.

                     

                    Jonathan

                    • 7. Re: Working without aggregations
                      Stuardo Herrera

                      Thanks for the response Jonathan. I finally opted to show all states at once, and let the user zoom in using the map controls. The purpose of the filter was to let the user zoom in at the desired State and to filter out all cities that weren't of that state.