I'm trying to find a solution/formula where when the Table.Field1 = A, I can then get a DistinctCount from Table.Field2. I've tried If/Then statements and While/Do statements.

For instance, I'll create a Formula Field called @Example and put in the formula: If {Table.Field1} = A Then DistinctCount({Table.Field2}). It doesn't seem to add up correctly.

Then when I do the following, @Example - While {Table.Field1} = A Do DistinctCount({Table.Field2}), I get a loop error.

I've also created it where I just have @Example to be {Table.Field1} = A and then in the report itself have it do a distinct count; however, it's adding up the wrong field.

I want to total amount of Table.Field2 where Table.Field1 = A.

Thanks.

For instance, I'll create a Formula Field called @Example and put in the formula: If {Table.Field1} = A Then DistinctCount({Table.Field

Then when I do the following, @Example - While {Table.Field1} = A Do DistinctCount({Table.Field

I've also created it where I just have @Example to be {Table.Field1} = A and then in the report itself have it do a distinct count; however, it's adding up the wrong field.

I want to total amount of Table.Field2 where Table.Field1 = A.

Thanks.

To find SUM of the field:

select sum(Table.Field2)

from Table

where Table.Field1 = 'A'

To find COUNT of the field:

select count(Table.Field2)

from Table

where Table.Field1 = 'A'

To find DISTINCT COUNT of the field:

select distinct count(Table.Field2)

from Table

where Table.Field1 = 'A'

Will this work.....or do you not have access to the Database..?

Yes, however, I also just figured out another way to solve my issue. Here is what I did:

Created in the Formula Field @Example1 where the formula reads:

If {Table.Field1} = A Then 1 Else 0

Then I went to the report to the Toolbar >Insert >Summary

Chose from the field to summarize: @Example1

Calculate this Summary: 'Sum'

Summary location: Group #?

And then clicked OK

From there, the @Example appeared in my report, and I just dragged it to the place where I wanted that result to be seen.

I was then able to do likewise for the opposite information where in the Formula Field @Example2 was created {Table.Field1} <> A Then 1 Else 0

Thank you for your input above.

All Courses

From novice to tech pro — start learning today.

Also, While/For loops are not stepped through once for each record. The entire loop is repeated every time the formula is evaluated. So, ' While {Table.Field1} = "A" ' would not do anything if the field in the current record was not "A", and would be an endless loop if the field in the current record was "A".

As for the formulas that you said you're using, they may work, but I don't think they'll give you exactly what you said you were looking for. You said that you wanted a distinct count of Field2 where Field1 is "A". What you're getting is just a count, not a distinct count. If Field2 is always different, then the two counts would be the same. But if Field2 is sometimes the same in different records where Field1 = "A", then a distinct count would be different (smaller).

For example:

Field1 Field2

A 1

A 2

A 2

B 1

B 2

Your count would return 3, for the 3 "A" records. But a distinct count of the values in Field2 in those "A" records would give you 2.

James