9 Replies Latest reply on Feb 22, 2013 1:21 PM by Jonathan Drummey

    filtering

    damian thomas

      Hi, I'm a newbie. My problem is filtering on date or percent.

       

      If records fall into specific date range show or percent greater the 50 show. Any examples or help would be great. Thanks

        • 1. Re: filtering
          Shawn Wallwork

          Damian, welcome to the forums! See attached for an example of how you can do this.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: filtering
            damian thomas

            Shawn, this is great. However, my results need to be limited by percent and date.

            • 3. Re: filtering
              Shawn Wallwork

              Just change from OR to AND

               

              And.png

              --Shawn

              • 4. Re: filtering
                damian thomas

                Shawn the formula works great. Tableau does not allow me to add it to a filter. I would use your example as:

                 

                (SUM([Profit])/SUM([Sales]))>.07 OR (ATTR([Order Date])>[Start Date] AND ATTR([Order Date])<[End Date])

                 

                My next step is to add it as a filter. This does not work. 

                 

                I need limit my result set based on the formula above.

                • 5. Re: filtering
                  Jonathan Drummey

                  Hi Damian,

                   

                  Just saying "This does not work." is not particularly helpful, please give us more information next time about exactly what you're doing and where you are seeing the failure. Based on your description, I *think* what's going on is that you created a calculated field with this formula:

                   

                  (SUM([Profit])/SUM([Sales]))>.07 OR (ATTR([Order Date])>[Start Date] AND ATTR([Order Date])<[End Date])

                   

                  And then tried to drag that onto the Filters Shelf, and Tableau didn't let you do that. Is that correct?

                   

                  If so, what's going on is that Tableau will allow you to put any disaggregated field on the Filters Shelf, and only continuous (green pill) regular aggregates, and pretty much any table calculation you want. The calculated field you created returns a boolean True/False aggregate, which Tableau classifies as a discrete (blue pill) aggregate and cannot be used on the Filters Shelf. There are a couple of workarounds. The one I typically use is to change it to an IF/THEN that returns something like 1 for True/0 for False and then filter for 1. Alternatively, you can wrap that calc in LOOKUP(thecalc,0) and use that as a table calculation filter.

                   

                  Jonathan

                  • 6. Re: filtering
                    damian thomas

                    Jonathan, I appreciate your reply. You understand my issue completely.

                     

                    In my reply "This does not work" was stated cleary after I mentioned I could not add the formula to the filter and was me just reiterating.

                     

                    Any who! I will take my newbie self and try your solution.

                     

                    Thanks

                    • 7. Re: filtering
                      Jonathan Drummey

                      Hi Damian,

                       

                      I'm glad we found a solution for you!

                       

                      To clarify what I'd prefer to see when someone describes a problem, it would include being more explicit about the steps they took and the issue they ran into, in this case something like:

                       

                      1. Created a calculated field "foo" with formula (insert formula here).

                      2. Tried to drag "foo" to the Filters Shelf.

                      3. Tableau wouldn't let me place the "foo" pill on the Filters Shelf.

                       

                      #2 and #3 are a lot more explicit than using "it" in different sentences to refer to possibly two different fields (Shawn's original field that returned a measure and your revision to a boolean) and "This does not work." Otherwise we (the responders) can get confused about what part isn't working and/or where the failure is occurring.

                       

                      I'm being a bit picky here and taking the time to write this to help increase the speed at which you get a useful response from the forums and as well as increase the efficiency of us (the responders) in helping you. Also, more than once I've found that the effort I take to be clear in composing a forum or tech support request leads to me figuring out a solution myself.

                       

                      Jonathan

                      • 8. Re: filtering
                        damian thomas

                        Jonathan,

                        Can you provide an example?

                         

                        Thanks

                        • 9. Re: filtering
                          Jonathan Drummey

                          Hi Damian, what would you like an example of? Are you asking for an example of the kind of explicitness I'm talking about? In that case, here's one: http://community.tableau.com/thread/123680 (though I would have preferred the original questioner to include a packaged workbook so Joshua wouldn't have had to put that together himself).

                           

                          Or are you asking for an example of something I figured out while working on a problem? Here's the most recent one I can find, I ran into this for my own work. I was going to email tech support and in the process of documenting what was going on I figured it out and wrote up a blog post on it: http://drawingwithnumbers.artisart.org/unexpected-results-aliases-in-url-parameters/.  (And still emailed support to ask them to update the docs).

                           

                          Jonathan