2 Replies Latest reply on Aug 15, 2012 9:42 PM by Jonathan Drummey

    Create filter based on number of records an additional field

    Adam Sexton

      To comply with government regulations, I've got to not show results for certain questions if the number of records is below 10. I've got a quick filter setup to let the user pick from any question in the database, but need to hide answers with less than 10 records only for certain ones. I've tried to combine this in an IF statement to filter on, but get the "can't mix aggregate and non-aggregate" error.


      IF [Question Number] = '33' AND [Number of Records] <10

      THEN "Cannot Use"

      ELSE "OK"



      Any suggestions?

        • 1. Re: Create filter based on number of records an additional field
          Mark Holtz

          Believe there are a couple ways to go about this.


          I think this would work:

          1) Make a parameter field as an integer and call it something like "Show Questions with at least n results"


          2) Make a calculated field (let's call it "RowCountFilter"):

          IF WINDOW_SUM(SUM([Number of Records])) >= [Show Questions with at least n results]

          THEN 'Show'

          ELSE 'Filter'


          (If [Question Number] is the only thing on the shelf, it should work fine, otherwise you'll need to go into the "Edit" box of the RowCountFilter calculated field and change "Compute Using:" from "Default Table Calculation" to [Question Number].)


          3) Add [RowCountFilter] to the Filters shelf and show only 'Show' values.


          Hope that helps.

          • 2. Re: Create filter based on number of records an additional field
            Jonathan Drummey

            Besides what Mark wrote, another thing to keep an eye on is whether the requirement is to have at least 10 responses or at least 10 _valid_ responses, e.g. skipping did not answer, invalid answer, etc. Depending on how your survey data arrives, Number of Records might be for all responses, in which case you'd need to implement a filter for valid responses. In that case, what I'll typically do is create a "# of Responses" field that has something like SUM(IF [insert validation test that returns True here] THEN 1 END) and use that for all subsequent totals and calculations. It's also helpful since that makes a nice default axis title.


            Also, if you are using additional dimensions in the view, for example breaking down responses to a question by a demographic dimension, then you need to figure out what is the minimum number of responses needed per member of the demographic. For example, I work with AHRQ physician office survey data and besides a minimum of 10 responses, when breaking it down by demographic such as position (physician, nurse, etc.) the requirement is to have at least 3 responses per member of the demographic in order to preserve anonymity, so there's an additional filter for that.