6 Replies Latest reply on May 17, 2013 1:21 PM by Jonathan Drummey

    having problems using parameters to filter on sum

    bruce lomasky

      I have a map which displays each customers total sales sum(sales).  I have 2 parameters, minSales and maxSales.  I want to only filter only the customers with sum sales between these values.  I created a calculated field "True to Show" with expression sum([sales]) > [minSales] and sum([sales]) < [maxsales] and I get an error that can not use this field on user defined aggregate.

       

      Help

       

      Bruce

        • 1. Re: having problems using parameters to filter on sum
          Joshua Milligan

          Bruce,

           

          You cannot use a discrete aggregate calculation as a filter because the aggregate result is dependent on what is filtered -- so it would create circular dependency.

           

          One possible solution is to add customer to the filter, Select the "Condition" tab and then use the expression you mentioned above.  That way, you are explicitly telling Tableau what level of detail to perform the aggregate and it avoids the issue described above.

           

          Regards,

          Joshua

           

          Filter.png

          • 2. Re: having problems using parameters to filter on sum
            Jonathan Drummey

            @Joshua - I'm not sure what you mean, we filter all the time with continuous aggregates (green pills with SUM, AVG, AGG, etc.). There is a limitation that we cannot put a discrete aggregate on the Filters Shelf, and this calc would return a boolean value, which Tableau classifies as discrete.

             

            Therefore, one option is to change the calc to:

             

            IF sum([sales]) > [minSales] and sum([sales]) < [maxsales] THEN 1 ELSE 0 END and just filter for values of 1.

             

            Jonathan

            • 3. Re: having problems using parameters to filter on sum
              Joshua Milligan

              Jonathan,

               

              I just reread my post and you're right.  But as I tried to think through how to clarify why one cannot filter using a discrete aggregation, I realized I don't fully understand.  What is the reason for the limitation?  SQL doesn't mind having a discrete condition in a HAVING clause.

               

              Regards,

              Joshua

              • 4. Re: having problems using parameters to filter on sum
                Jonathan Drummey

                I don't know, it's something I've always wondered about, especially since we can put the table calculation LOOKUP([some discrete aggregate],0) on the Filters Shelf and that totally works. My only guess has been possibly something about loading the domain for the filter being an issue, maybe Dustin Smith could enlighten us?

                • 5. Re: having problems using parameters to filter on sum
                  Dustin Smith

                  First - thanks for the compliment in looping me in here.  The abstracts of why Tableau queries the way it does isn't my strong-point by any stretch, but noodling on this led me down some interesting paths.

                   

                  First Answer:  KB article (assuming you've both already seen this, but posting for posterity)

                  Adding Calculated Field to Filter Shelf

                   

                  Specifically this part from the KB:

                  Note: To handle aggregated calculations on the Filter shelf, Tableau uses a variation of Multipass SQL. However, because many databases allow only numeric or simple operations, Tableau does not support aggregated calculations that contain both numbers and strings.

                   

                  More in-depth answer thanks to internal resources (Jedi Marc Rueter to be exact):

                   

                  Discrete aggregate values are unreliable in many cases – meaning they are, by definition, dynamically computed and variable.  How do we get the domain of the field for you to pick from?  Which level of detail do we use? etc.  What happens if the level of detail of the viz changes, unlikely that value will still exist.

                   

                  Here is an example that we can figure out, but is hard for Tableau:

                   

                  IF sum(sales)>10000 then "good"

                  elseif sum(sales)<100 then "bad"

                  else "OK" end

                   

                  What query does tableau run to determine what the possible values are to put into a quick filter?

                  The ability to use discrete aggregate table calcs in filters hopefully gives you guys confidence that it's not something we're ignoring.

                  1 of 1 people found this helpful
                  • 6. Re: having problems using parameters to filter on sum
                    Jonathan Drummey

                    Thanks for this, Dustin, it's in line with what I was thinking. However, from a selfish user point of view, Tableau is already so close to having this functionality (though I recognize it could take a lot of developer thought and time to implement):

                     

                    - As you pointed out, Tableau has already tackled these questions within table calculations, where the domain is the level of detail of the view, and the quick filter is all the possible values. However, table calculation filters are processed late in the pipeline and bring a number of complexities that we want to avoid for the "simple" Show/Hide case.

                     

                    - Filters on continuous aggregates are normally processed in the HAVING clause in the data source, while dimension filters go into the WHERE clause. However, when we're using a blend with a calculated field in the primary that pulls data from the secondary like SUM([Sample - Coffee Chain (Access) 8.0].[Sales]), the computation in the primary for the blend is happening inside Tableau (not the SQL) using data pulled from the data source based on the blending fields. A filter on that field is processed inside Tableau after the blend, again at the level of detail of the view, and a quick filter uses all the values of the cells in the view. So Tableau already has a capability to compute the domain, generate the new fancy v8 Quick Filter range display, and apply the filters. I bring this up to point out that if the stumbling block is trying to push as much of the computation as possible into the data source, Tableau already has a precedent for doing computation itself, hiding the complexities, and reducing the data set for further processing (like table calcs).

                     

                    - We can work around any discrete aggregate filtering problem by turning the discrete into a number. Why can't Tableau get a little smarter and just do that for us behind the scenes for booleans, at the least, where it's totally clear the domain will never have more than three values (True, False, and Null)?

                     

                    If this were implemented, one hard problem is definitely the interaction between Quick Filters and Show All Values/Relevant Values, I don't have anything to say on that other than I'm already used to there being complexities there (though the complexities that drive me the most batty are the ones where the quick filter is being computed in the cache or data source while the data is coming from the other).

                     

                    Ok, rant off. I just created an idea for this at: http://community.tableau.com/ideas/2190