Aggregate Filters

[jeff_crisp]

I am struggling with a report build and I am hoping the community can help me out.

I run a simple report with 4 fields and my data looks like the following:

Field1	Field2	Field3	Field4
Name1	0	0	Long Name 1
Name2	0	1	Long Name 2
Name3	0	0	Long Name 3
Name4	1	1	Long Name 4
Name5	1	0	Long Name 5
Name6	12	1	Long Name 6
Name7	13	0	Long Name 7
Name8	14	1	Long Name 8

I apply a break over Field2 so that values are grouped together.  I then apply a count over Field 1. 

I get everything fine to this point, but what I am then wanting to do and only return data where the count(field1) is greater than 1.  I attempted to create a variable that was COUNT(Field1) and apply a filter over that, but it excludes everyone because it is just counting that row and not the whole table.

Any advice on how to do this?

[amitrathi239 · Accepted solution]

Hi,

Create variable  Var==Count([Field1]) In ([Field2])

Apply Filter on Var is greater then 1.

It will exclude the values which are equal to 1 or less.

Amit

[jeff_crisp · Accepted solution]

Amit,

I created a variable based on your explanation, but I think something went wrong.  Here is my calculation:

=Count([field1] In ([field2]))

When I add this variable to the report, it just shows 1 for the distinct values.  For the rows that are duplicated, I get 0 for those values.  I can work with this and just apply my filter where the variable is equal to 0, but I am not sure why I did not get the count number like you were stating I should get.

Thanks

[amitrathi239 · Accepted solution]

Hi,

use this.

=Count([Field1]) In ([Field2])

in your formula  closing bracket of count is in after field 2.With this even i am getting 1 or 0 records.

=Count([field1] In ([field2]))

Try with my above solution.

Amit

[jeff_crisp · Accepted solution]

Worked like a charm.  Thanks!