1 Reply Latest reply on Mar 12, 2012 1:51 PM by Tracy Rodgers

    Problems creating a filter using True/False logic

    bruce lomasky

      I have 2 calculated fields, Current product mix, and prev. product mix.  They are both aggregates COUNTD(ProductID).  I have a 3rd calculated field, % Growth which is (current-prev) / prev.  I want to filter out all items with no (zero) growth and figured I should be able to create a calculated field (%growth <>0) and use this T/F on the filter shelf.  However, I can NOT drag it to the filter shelf.  Can someone tell me why?


      2nd part of  the question, if I change my calc to IIF(%growth <> 0,1,0) then convert this calc to Continuous, I can drag it to the filter shelf and set the MIN value to 1.  This works, but why does this work and the T/F does not.





        • 1. Re: Problems creating a filter using True/False logic
          Tracy Rodgers

          Hi Bruce,


          Discrete aggregations cannot be filtered because of a query limitation. The filter has to be part of a  'having' clause or part of a multi pass SQL. Tableau writes the results to a temp table, then the temp table is filtered. Most databases only support numeric comparisons in a 'having' clause, so it cannot be added there; and Tableau does not support true multi-pass SQL.


          Therefore, the workaround is, as you found, making the calculation continuous. Another calculation that could be used is:


          if [%growth]<>0 then 1 else 0 end


          Hope this helps!