7 Replies Latest reply on Apr 14, 2017 1:32 PM by Yuriy Fal

    I can't search for it if I don't know what it's called!

    Catherine Tulley

      Dear all,


      I am having a terrible brain-fart today and cannot think of the right language to use re: finding a way to calculate a particular feature request.


      Say I have a table, SALES, that has many columns of sales metadata. Each row is a city in the sales region. The table can be grouped or filtered as the entire sales region, by individual sub-region (county), or individual city. The dashboard includes a map, a city Top/Bottom 10, a county graph, and what I am trying to attempt is a multi-geography graph. I am probably overthinking this or something, but I just can't figure out how to do this.


      I need to be able to show a bar graph with a regional sales total, a county total, and a city total. Ideally, when the user first loads the dashboard, only the regional total would be displayed, and the other two would be blank until the user selects a location on the map. They can't be in separate worksheets because the requestee didn't like how that looked, and the axis needs to be consistent for the 3 items. A drill-down hierarchy won't work, either.


      Right now, the closest I've gotten is a hidden view that only appears with all 3 bars when the user clicks a city on the map. The map supplies the graph with the county and city selection, and activates the graph view via a filter action. I am populating the graph using Measure Values with Region, County, and City selected, and then the entire dashboard is being driven from a parameter list. It works, but it's klunky and other users might misunderstand it.


      I also need to use this 3-level concept in a different table to display a bunch of metadata from different fields.


      Is there a better way to accomplish this, and a term or concept that I am missing?? I've been staring at this too long and I am probably overthinking it.



        • 1. Re: I can't search for it if I don't know what it's called!
          Shawn Wallwork

          You're describing the type of filtering that QlikView offers. I was asked to emulate this cascading filters behavior and was never able to get it close enough for the client to accept it. One year later the same client came back and said he wanted to start from scratch using Tableau's strength and features. Of course, every iteration I produce that used Tableau filtering was rejected with, "I can't believe Tableau can't do something so simple."


          Hope you haven't accidentally run into the same guy.



          • 2. Re: I can't search for it if I don't know what it's called!
            Yuriy Fal

            Hi Catherine,


            It is a chance that I've totally missed

            your requirements, anyway ...


            Please find the attached.




            • 3. Re: I can't search for it if I don't know what it's called!
              Catherine Tulley

              Hey guys, thanks for the replies! I blinked and two weeks went by...sorry for the belated response.


              Shawn, I got a good laugh from your comment because someone says those exact words in almost every Tableau meeting we have. I hope that they implement this in future releases, because it's maddening to not have it available!


              Yuriy, your workbook is very helpful. Thank you for sharing that idea as I learned some new things from it. (Haven't had a chance to use Include/Exclude statements yet, so I'm going to have to study them for a bit.)

              • 4. Re: I can't search for it if I don't know what it's called!
                Catherine Tulley

                Hi Yuriy, I wanted to see if I could pick your brain again about getting a feature to work. I'm attaching a copy of my workbook. (Yes, I am still working on this **** thing.)


                I made good progress with getting the filter to work, based on your input. In the middle bar graph, it shows the same value for all 3 geographic levels. When the user clicks on the map, filters the 3 bars to the region, county, and municipality that was clicked.


                I have this filter action working the way that I want, but I can't seem to figure out how to get combination of the parameter calc and the FIXED LOD calc to return the right values across the board. The list parameter has 20 measures; each one is a field. What I really need to be able to do (I think) is call MIN([Field]) where County = Foo and Municipality = Bar.


                SUM() is working (mostly) for the absolute values, but it's returning the wrong numbers for the percentages, and that's kinda where i am stuck.


                If you have any ideas or suggestions, I will be eternally grateful.



                • 5. Re: I can't search for it if I don't know what it's called!
                  Yuriy Fal

                  Hi Catherine,


                  The 20 Measures you've choosen via Parameter

                  are all hard-coded in the dataset -- including Percentages.

                  The Percentages are valid when selected at the Row Level,

                  but aggregating them (in any way), the result is wrong.


                  To get correct numbers for Ratios, one should calculate them as

                  SUM( [Numerator] ) / SUM( [Denominator] )

                  where both Numerator and Denominator are additive values

                  (mean they can be summed up along any Dimension).


                  For the Percent Difference the calc could be as above with a little mod:

                  SUM( [Numerator] ) / SUM( [Denominator] ) - 1


                  Hope you get the idea.


                  As an example, I've made changes to your workbook,

                  making the calculation # 04 (from the Measure Selector)

                  as described (for both County Graph and Multi-geo Graph views).


                  Please find the attached.

                  Please look at these views and the calcs named as [YF : ....].


                  Hope it could help.




                  1 of 1 people found this helpful
                  • 6. Re: I can't search for it if I don't know what it's called!
                    Catherine Tulley

                    Thank you very much for the reply! I'd been thinking...I should probably be calculating this on the fly, but I was asked to hard-code everything first and see if I could get it to work that way. I was stuck in "Excel logic" and trying to figure out how to force it to do the equivalent of a cell lookup. This is so much simpler! (Forest for the trees, etc. *facepalm*)


                    Thanks again for your help.