1 2 Previous Next 17 Replies Latest reply on Dec 2, 2013 7:31 PM by Doug Erickson

    non aggregated method to determine if field is filtered

    Doug Erickson

      I have a unique data structure that requires a Boolean filter that is set based on the condition of another field being filtered.  So the logic would look something like this. 

       

      integer_Field_A == if isfiltered(Field_1) then 4 else null end

       

      Typically for the isfiltered function I would use countd(Field_1)  == 1 but then I get an error for using aggregated and non-aggregated values in an 'if' statement. 

       

      Is there a way to write a Boolean filter using aggregated functions or is there a way to determine if a field is filtered using a non-aggregate method?

        • 1. Re: non aggregated method to determine if field is filtered
          Doug Erickson

          one last bump, before I conclude that this is not possible. 

          • 2. Re: non aggregated method to determine if field is filtered
            Andy Piper

            Doug,

             

            Is there any way you can post a sample workbook of what you have? Your formula is referencing an isfiltered function which does not exist in any version of Tableau that I've ever used.

             

            Andy

            • 3. Re: non aggregated method to determine if field is filtered
              Doug Erickson

              Hello Andy, 

               

              'isfiltered' is a fictional placeholder to demonstrate the logic sequence.  I am hoping for find a formula that will create that result.  One method is to use countd(field) == 1 to determine if a field has been filtered, but that is an aggregated function and will not work when mixed with a non-aggregated IF statement.

               

              Currently I am using parameters instead of fields to set my filter, that way I can test for the single value of 'All' that is in my parameter values.  But that prevents me from being able to use quick filters that only show relevant values.  i will try to put a sample book together that has a similar setup to illustrate the problem. 

              • 4. Re: Re: non aggregated method to determine if field is filtered
                Alex Kerin

                @I think you can do this with size(), as long as you know what the unfiltered size is. Attached 8.1 workbook. Try unclicking a category. The calc is: attr([Department])=if size()<17 then "Furniture" end

                • 5. Re: Re: non aggregated method to determine if field is filtered
                  Alex Kerin

                  I'm sure you could also get the '17' from elsewhere as well.

                  • 6. Re: Re: Re: non aggregated method to determine if field is filtered
                    Jonathan Drummey

                    Alex - such as using a self-blend, I set up an example in the attached where there is no blend between the primary and secondary, so the secondary copy of the data doesn't get filtered by the Category.

                     

                    Doug - I'm pretty sure there is a solution, if what Alex put together doesn't quite fit what you need then please do mock up some data (or use superstore sales) and an outline of the interactivity you want.

                    • 7. Re: non aggregated method to determine if field is filtered
                      Doug Erickson

                      Thanks for the suggestions, I will dig into them over the weekend and if not quite right then I will post a mock up to use for testing.

                      • 8. Re: non aggregated method to determine if field is filtered
                        Doug Erickson

                        WARNING ! this is a long post, continue if you dare

                         

                        Ok, I looked at the size solutions, but it does not seem to get around the aggregation problem.  Here is the  sample workbook and an explanation of the data structure.

                         

                        The core data is a single measure(revenue) with three core dimensions (geo, product, sector). Inside each of  the dimensions are values for multiple hierarchy levels, i.e. geo units: country, region, continent. For  example, in the single column there will be a row for Finland(country), Nordics(region) and Europe(continent)  and similar levels for product and sector. This means the levels are not aggregatable (the sum of the countries  does NOT equal the sum of their region). Each level in the dimension has it's own revenue total.

                         

                        This structure turns the queries into more of an index request, requiring a filter for each dimension even if  it is for the total, since 'Total' is actually a row value. So a query to return the revenue amount for Finland  for all products in all sectors would be: 'Select revenue From table Where geo = Finland and product = Total  and sector = Total'

                         

                        To be able to do better visualizations I created a pseudo-hierarchy in the data. First I added a column(xx  field) for each core dimension with integer values that designate which hierarchy level the geo unit is for  that row. I used integers for better query performance.  I then added more columns to create the pseudo hierarchy, xx code(integer) and xx name(text) for each level in  each core dimension. These are filled in to match the relationships between the different levels in the  hierarchies.

                         

                        I then created a calculated field (Series field) that allows the user to select which dimensional unit they want to see on the graph i.e. country, item, region, department, industry or group, via a parameter selection (param-series). The calculated field will result in the correct column to use on the worksheet.

                         

                        Now we need to have two filters for each of the three dimensions need to return the correct value. The geo, product and sector filters limit the data to the desired scope. The series level filter a, b and c set at which hierarchical level the dimensions are being filtered at (this eliminates nulls and double counting).


                        Currently I am using parameters in place of the fields to create the filters as I can retrieve the parameter value without needing an aggregated function. It is the series level filter calculated fields that I want to replace with quick filters with showing only relevant values. Hopefully the field formula will show what I am trying to do. 

                         

                        Thanks

                         

                        Erick

                        • 9. Re: non aggregated method to determine if field is filtered
                          Doug Erickson

                          Sample workbook is posted.  Thanks

                          • 10. Re: non aggregated method to determine if field is filtered
                            Doug Erickson

                            Hello Jonathan,

                             

                            Sample workbook is posted

                            • 11. Re: non aggregated method to determine if field is filtered
                              Doug Erickson

                              Thanks Alex,

                               

                              Sample workbook posted.

                              • 12. Re: non aggregated method to determine if field is filtered
                                Jonathan Drummey

                                Hi Erick,

                                 

                                I don't understand your comment "I can retrieve the parameter value without needing an aggregate function." The data has all dimensions and the series level calculated fields are all dimensions, so there's no aggregation necessary in your calculated fields.

                                 

                                Beyond that, I think the difficulty you are having is due to a couple of factors:

                                - The first is having data that is pre-aggregated. Tableau likes to do the aggregations and we have to go through some contortions to not aggregate the data.

                                - You're trying to provide a "kitchen sink" sort of view where the user has all the filter options possible. Tableau just isn't oriented that way, so trying to set that up that gets more difficult. Here's one thing you'll run into if you continue in this direction - when you're using Only Relevant Values, all the filters with that turned on are computed at once. So if a user filters for a certain value in dimension A that doesn't exist when a certain value of dimension B is selected, the view goes blank *and* the filter for dimension A stops showing that selected value. The user has to then know to make a selection on dimension A to get something to appear again. When you're looking at potentially 6 different filters with Only Relevant Values, the user has to know what exists in the data to get anything to show up. In addition, for large data sets with high cardinality in the dimensions for Quick Filters this can make the view really slooooowwww. Honestly, this is one of the (few) areas where QlikView is faster/easier than Tableau, and from my limited experience with QV this is how people are encouraged to build views. However, the true validity of this sort of view is questionable because users will only see what they know to filter for. Robin Kennedy did a nice blog post on this at How NOT to use Tableau - The Information Lab.

                                 

                                I think it's useful to take a step back and look at what your users are really trying to do (not so much what data they want to see, but what they want to do with what they learn from looking at the data), and then use that to build some views. For example, right now the colors have no real meaning other than to separate the lines. If you set up more guided analytics, then there would be a chance to use some specific palettes and users would have more information/guidance about what they are looking at.

                                 

                                Jonathan

                                • 13. Re: non aggregated method to determine if field is filtered
                                  Doug Erickson

                                  Hello Jonathan,

                                   

                                  Thanks for the comments and I understand what you are saying. 

                                   

                                  In regards to the confusion around 'aggregation' I used the wrong term, what I need is to do table calculations on the dimensional fields. This requires tableau to aggregate the data values to determine count or max. This in turn breaks my filter formula as it mixes aggregation and non-aggregation functions together. 

                                   

                                  On your other point, yes the problem is the data is pre-aggregated.  Unfortunately, that is the only way I can get the data.

                                   

                                  On the 2nd point you are correct regarding the kitchen sink. The workbook is being designed for do-it-yourself visualization of various graphs, not an analysis dashboard. It will have 100's of users with different areas of focus, (region, product or sector.  Plus different levels of hierarchy, (overall to individual items), thus the kitchen sink approach. 

                                   

                                  I have seen first hand the impacts for quick filters, fortunately I will be able to calculate three of the six filters leaving the user to set only three.  Hopefully with a distributed server environment, integer filters and a not excessively large dataset (1.5M rows) the performance will be tolerable. 

                                   

                                  I am inspired by your last paragraph to continue to find better solutions.  But for now, the above description is what the client is looking for,

                                   

                                  Erick

                                  • 14. Re: non aggregated method to determine if field is filtered
                                    Jonathan Drummey

                                    I'm pretty sure you can keep your filter formulae as dimensions, then aggregate those dimensions. Can you describe the kinds of aggregations you're trying to do?

                                    1 2 Previous Next