3 Replies Latest reply: Jun 14, 2011 4:19 AM by Stephen Redmond

# Filter Objects on Tab Only (Not Globally)

I've got some data that is calculated based on a realtive pool. In the example below, I've got data that includes student name and number of siblings. I want to know what percent of total siblings each student has relative to all students in the class. The tables below demonstrate this concept. As you change your filters, the relative pool size would adjust.

Figure A: Global Filter

Let's say that in my class there are four students: Matthew, Mark, Luke, and John. They have a total of 15 siblings. The data would look like this.

StudentNumber of SiblingsPercent Total Siblings
Matthew427% (4/15)
Mark320% (3/15)
Luke640% (6/15)
John213% (2/15)
TOTAL15100% (15/15)

Figure B: Filter 2

Now let's say I just want the pool to consist of Matthew and Mark. The data would look like this:

StudentNumber of SiblingsPercent Total Siblings
Matthew457% (4/7)
Mark343% (3/7)
TOTAL7100% (7/7)

So far I've done all this easily and successfully. My challenge is to create a separate tab that highlights each student individually, relative to the total class. This table would be driven by a list box or variable input where you select which student you want to profile. So say I wanted to just view Matthew. I would select his name in a list box or variable input to get the following table. What I want to do here is only filter the objects on this sheet, not apply a global filter that is going to mess with the relative pool. Does that make sense?

So I pick Matthew from a list... and it basically shows me Figure A but just for Matthew. How would I do this? Is there a way to set a filter that only applies to objects on a certain tab but doesn't actually play into the total global filter? Thank you sincerely for your thoughts and feedback. I would share the document itself but it contains sensitive information so I can only speak using this example for now.

StudentNumber of SiblingsPercent Total Siblings
Matthew427% (4/15)

• ###### Filter Objects on Tab Only (Not Globally)

Hi,

I am guessing that your % calculation is something like this:

Sum([Number of Siblings]) / Sum(TOTAL [Number of Siblings])

So, you could exclude selection on the Student field by including it in a set like this:

Sum([Number of Siblings]) / Sum({<Student=>} TOTAL [Number of Siblings])

Regards,

Stephen

• ###### Filter Objects on Tab Only (Not Globally)

This did help in many instances. However it does not seem to work when I'm using a Sum( TOTAL AGGR  (()) function. Is there a way to make this work with those types of functions as well?

• ###### Filter Objects on Tab Only (Not Globally)

Hi,

You probably need to include an appropriate set in the Sum( AGGR) also.

Regards,

Stephen