3 Replies Latest reply on Jun 20, 2013 7:31 AM by Alex Kerin

# Filter - show all children of a parent, if one or more children match a criteria

Hi everyone,

I want to create a filter that returns all the (children) dimension of a (parent) higher-dimension, if one of the children meets the test criteria. In this sample data set, the test is: "are there 1 or more  chickens in a barn?"

Easiest to explain with a sample dataset (twbx attached).

 Barn Animal Headcount ChickenCount A Chicken 10 10 A Cow 1 0 A Pig 1 0 B Cow 5 0 B Pig 5 0 C Chicken 20 20 D Chicken 1 1 D Pig 5 0

There are 4 barns (A,B,C,D), each having one or more animals types (chicken, cow, pig) .

I want to create a filter that identifies which barns contain 1 or more chickens (answer: A, C, D)... but what makes it tricky is I want to still see what other animals are in the barn with the chicken.

The final result should look as follows (Barn B is filtered out, but A, B, D remain and all the animals within)

 Barn Animal Headcount ChickenCount A Chicken 10 10 A Cow 1 0 A Pig 1 0 C Chicken 20 20 D Chicken 1 1 D Pig 5 0

AP

• ###### 1. Re: Filter - show all children of a parent, if one or more children match a criteria

We can use a window_sum that sums all the values for your number of chickens within a single barn, as attached.

Note the table calc (right click) compute using is set to "Animal" so the sum operates on each barn. WIth your real dataset you will likely need to set the advanced properties so that the only discrete pill on the left is barn:

Great tbwx and question framing - makes it so mush easier...

1 of 1 people found this helpful
• ###### 2. Re: Filter - show all children of a parent, if one or more children match a criteria

Alex,

Thanks for the very quick response, and yes that's getting very close to  the desired result with all the fields showing - but the GRAND TOTAL still add the animals from barn B (the total should be 38, but it's still counting 5 pigs + 5 cows in Barn B and showing 48)

Also, is it possible to achieve the same effect WITHOUT having animal-type showing, and just summing the number of animals in each barn (not specifying how many of each type, just the total). Leaving ANIMAL in the detail shelf seems to create a situation where multiple numbers appear per cell, rather than summing them together)

A               12

C               20

D               6

thanks again,

AP

• ###### 3. Re: Re: Filter - show all children of a parent, if one or more children match a criteria

So to the second point, yes with another quick calc and putting Animals on the level of detail. Now we have to customize the grand total (which means I have to reread these from Jonathan Drummey (Customizing Grand Totals – Part 1 | Drawing with Numbers)