14 Replies Latest reply on Oct 23, 2018 1:16 AM by Simon Runc

    Creating a filter based on formula?

    Glen Park

      Hello everyone,

       

      I'm very new to Tableau and having the toughest time applying a filter on a string column.  Basically I'm looking to filter out any rows from my source where the column contains any one of two substrings.  I drag my column into the filters window then in the "Condition" table I choose "By Formula" and I put the following:

       

      CONTAINS([my_column],"VRI") OR CONTAINS([my_column],"TRAN")

       

      Then I go into the "General" tab and check "Exclude".  But what happens is that my entire chart disappears.  Does anyone know what I'm missing?

       

      Thanks,

      Glen

        • 1. Re: Creating a filter based on formula?
          Mark Holtz

          Hi Glen,

           

          There are plenty of ways to go at this. I must say I'm not very familiar with using the custom aspects of the filters...I'm partial to creating calculated fields to hold formulas rather than inserting them directly into the filter. That way, I can expose the result in the view if I so choose.

           

          You could try something like this:

          IF CONTAINS([DimensionField],'VRI') OR CONTAINS([DimensionField],'Tran')
          THEN 'Filter'
          ELSE 'Show'
          END

           

          Then, just drag that new field into the filter shelf and choose to expose 'Show' only.

           

          Good luck!

          2 of 2 people found this helpful
          • 2. Re: Creating a filter based on formula?
            Alex Kerin

            Mark is right - you could actually use your original formula as a calculated field - it would evaluate as a boolean T/F

            • 3. Re: Creating a filter based on formula?
              Glen Park

              Yeah this is the way I ultimately went with.  Thanks!

              • 4. Re: Creating a filter based on formula?
                Rupali Negi

                Hi Mark,

                I have a question based on filter condition. I have a list of URL's with sum of page views.So I have url's in my row and there sum of page views as column. Now I have to exclude few Url's based on a specific condition, for which I created calculated field and done so. Now as a result in below screenshot I have list of Url's with respective page views and excluding what I need. Here the problem arises. I want to have top 10 of these Url's. Now I want top 10 Url's out of this view.

                 

                The problem I am facing is if I find top 10 Url's from the calculated field "Page URL Filter" it does not give top N row. If I filter original dimension Page URL decode it gives only 6 of them. I think because it is filtering on entire data and as my few URL's are excluded in calculated filed so it gives just six.

                 

                How can I get the top N url's when I have used a calculated field filter on the same dimension as used in my row.

                 

                Any help appreciated..!!

                Thanks

                Lucky

                • 5. Re: Creating a filter based on formula?
                  Ted Hopton

                  I have the same problem as Rupali.

                   

                  Can anyone point me to a good resource for learning how to write custom calculations that include filters?

                  • 6. Re: Creating a filter based on formula?
                    Simon Runc

                    hi Ted/Rupali,

                     

                    So in your calculated field, which excludes certain elements, and is used to filter...if you change the filter to be 'Add to context' that will push the filter up the calculation pipeline and so get applied before the Top N is.

                     

                    In an example here I show the Top 10 customers, just using the Top 10 filter on SUM([Sales])

                     

                    I then create a filter calculated field to exclude certain elements (Sean and Tom)

                    add this to the filter shelf and set to true...and now I only have 8 people in my top 10...as you've experienced

                    However I now set the [dummy filter] to 'Add to context'

                    and I'm back at 10 again, minus Sean and Tom

                     

                    an alternative on this theme, would be to create a calculated measure for your filtered list. so in my example I've done

                    [Sales Exclude Filtered]

                    IIF([dummy Filter],[Sales],NULL)

                     

                    I then use this in my Top N filter (not the original SUM([Sales]) measure, and again I'm back to 10 members, minus Sean and Tom

                     

                    Hope that does the trick for you, else for full Top N flexibility you'll probably need to use RANK or INDEX, which (as you say) means you'll need (in my example) [Customer Name] in the VizLoD.

                    4 of 4 people found this helpful
                    • 7. Re: Creating a filter based on formula?
                      Rupali Negi

                      Hi Simon,

                      Thanks for the detailed answer that helped. Although I have to submit it little earlier.

                       

                      Regards

                      Lucky

                      • 8. Re: Creating a filter based on formula?
                        Simon Runc

                        Although I have to submit it little earlier

                        ...not sure I understand...are you referring needing the filter applied earlier than a context?...

                        • 9. Re: Creating a filter based on formula?
                          Rupali Negi

                          Sorry for misunderstanding. Just wanted to say I submitted the assignment two months back..

                          • 10. Re: Creating a filter based on formula?
                            Simon Runc

                            No probs!...just wanted to check you had the solution you need!...I hadn't realised it was such an old thread!

                            • 11. Re: Creating a filter based on formula?
                              S Shah

                              Hey Mark

                              I have almost similar situation but my filter does not work.

                              So I have created a custom layout filter based on parameter with set of values, which has hierarchy as well. In the code for parameter, I have upto 4 character codes. From the database / query, I have result on the sheet, and when I put the field on filter card, set the Condition -> By Formula -> as "Contains ([FindStateVal], [StateCodes]), for all selections it work, but when I select "All", it blanks out. This is because there is no such value in StateCode field as "ALL".. That All needs to be all the values to be used.

                               

                              Is there a way to write conditional criteria in Filter screen somewhere to ignore CONTAIN formula in case of "All" ?

                              The issue is I have hierarchy of Area -> Region -> State and instead of showing 3 filters on dashboard which would take up lot of space, I had thought of this approach.

                               

                              Any help would be greatly appreciated..

                              S

                              • 12. Re: Creating a filter based on formula?
                                S Shah

                                How does the Contain filtered set would work, if someone selects the value with "VRI"? I replicated that for my situation and it works fine when "All" is selected, but otherwise blanks out. I actually posted my situation.

                                • 13. Re: Creating a filter based on formula?
                                  cesar gomez
                                  So I have an excel file with a column with text. I want to be able to search that column based on certain parameters  and then label it into a specific  "bucket" label.  Below an example of the terminology I am looking to filter but don't know how the terminology will work on a If and else statement.  Here is how I want to filter out to specific Label  by word searching an excel column:
                                  ("eng" or "eng1" or "eng2" or "eng3" or "eng4" or "engine" or "engines" ) and ("shut down" or "shutdown" or "IFSD") and ("atb" or NEAR(("emerg*", "declar*"), 2, FALSE) or "diver*" or "turn back" or "turnback" or "turned back" or "mayday" or "may day" or "21.3") and not (NEAR(("no", "shut*"), 2, TRUE) or NEAR(("not", "shut*"), 2, TRUE) or NEAR(("apu", "shut*"), 2, FALSE) or NEAR(("no", "emerg*"), 2, TRUE))   I Thank you ahead of time.
                                  So I got Half way there but I still need help below what I got so far: IF (CONTAINS([Event Description],'eng') OR CONTAINS([Event Description],'eng1')OR CONTAINS([Event Description],'eng2')OR CONTAINS([Event Description],'eng3')OR CONTAINS([Event Description],'eng4')OR CONTAINS([Event Description],'engine'))
                                  AND  CONTAINS([Event Description],'shutdown' )
                                  THEN 'Engine Shutdown'
                                  ELSE 'TRY AGAIN'
                                  END
                                  My problem is that I need to add more 'keywords' to the AND part of the formula as  OR type. For Example : AND CONTAINS ([Event Description], 'Shutdown' or 'Shut down' or 'Emer' or......)
                                  HELP

                                   

                                  • 14. Re: Creating a filter based on formula?
                                    Simon Runc

                                    hi Cesar,

                                     

                                    So with regards the ORs and ANDs...you can use brackets to group ORs within ANDs. So in the example you gave

                                     

                                    (some logic...

                                    AND

                                    (CONTAINS ([Event Description], 'Shutdown' or CONTAINS ([Event Description],'Shut down') or CONTAINS ([Event Description],'Emer'))

                                    )

                                     

                                    So in this case the IF statement will only equate to true if

                                    some logic...=TRUE

                                    AND

                                    any of the CONTAINS ([Event Description]) logic are true.

                                     

                                    One other thing that might remove a few tests, would be to upper the [Event Description] and then the CONTAINS test

                                     

                                    eg.

                                    CONTAINS (UPPER([Event Description]), 'SHUTDOWN')

                                     

                                    Hope that helps