4 Replies Latest reply on Oct 28, 2017 9:21 AM by Ryan Spencer

    Keep Only the Compliment when Multiple Filters are Selected

    Ryan Spencer

      Hi all,


      I've been struggling with this problem for a few weeks now:


      The white dot is the regularly filtered dot (All ages, Men, in All Regions).


      The black dot, currently, is just the unfiltered version of the white dot (All ages, All Genders, All Regions). This is for a certain school that has selected the comparison to be within their own school (rather than the national comparison).


      We would like to get the black dot to be more than just the unfiltered version of the white dot. We would like for it to take on the compliment of all parameters as long as the parameters don't have the "All" option selected.


      This is for a much larger, confidential, dashboard that has over 10 parameters, so we won't be able to add any of the para_* filters to context.


      If there are multiple parameters selected (for instance Males from the West region), we would still like to take the compliment of each of those (so still All Ages, Females, and East&Central).


      I've attached the example dashboard for your reference, and the black dot calculation can be found in avg_num_ans_natl. Let me know if you need any further clarification!

        • 1. Re: Keep Only the Compliment when Multiple Filters are Selected
          Jim Dehner

          Hi Ryan

          Interesting problem - Full disclosure - I am not statistically savvy - that said I think you can get the complement byt using the approach outlined below

          since the Fixed formula sets up all the permutations of those dimensions that preceed the colon (:) by using <> against the actual dimension and the parameter I think it will work




          It yielded the text table shown below




          I did not carry it through to the other calcualtions but you get the idea


          I do have a question - I looked at your data and saw 2-3 duplicates for each student ID - is that something you did just because the data is confidential or is it the result of some data collection process


          In any event it doesn't make business sense -



          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Keep Only the Compliment when Multiple Filters are Selected
            Ryan Spencer

            Hi Jim,


            Thanks for the reply! I'm not entirely sure this is working as expected... I've never encountered the "<>" operator before, what does this do exactly?


            I tried this out in my actual dataset, and I'm not seeing any change...


            Maybe I wasn't clear in what we're looking to accomplish. With the parameters selected the way they are (in the workbook I uploaded), I want the black dot to represent females, and the white dot males. But if the Gender parameter is set back to "All Genders", the black dot should represent both genders.


            It gets more complicated when we add in the rest of the parameters, but I think starting with the simplest case would be best.


            Let me know if this needs more clarification! It's definitely a tricky problem.





            p.s. There are duplicate student IDs since the original data is collected on a yearly basis, so a student might be surveyed twice. Thanks for bringing that up, though!

            • 3. Re: Keep Only the Compliment when Multiple Filters are Selected
              Jim Dehner


              thanks for the replay - the <> is Not Equal or in this case "does not include" - I am not a statistician so I can comment on the impact in the data other than to not that it was filtering out the total count when comparing the "Original" and revised version.

              As to the duplicat IDs my question would be what is unique in your data set that you are using to to aggregate your data around?  I'm seewing cases in yor data wwhere the same student ID refers alternately to male and female gender.



              • 4. Re: Keep Only the Compliment when Multiple Filters are Selected
                Ryan Spencer

                Hi Jim,


                I figured the <> was Not Equal, thanks for clearing that up!


                The duplicate ID issue is not relevant in this problem. If you'd like, you can delete them in the original data. They were there for previous tests and I never deleted them.