Count different string values in a group

John-S Pretorius
John-S Pretorius used Ask the Experts™
on
I am grouping by a specific field (Transaction Number), within each group I have a specific value that should always be the same within that group. The total group count varies, I want to count how many different values of this specific string are in each group:-

11000012956  (The group count should be 1 as the value is the same within the group)
AA12BBCC
AA12BBCC
AA12BBCC
11000012957 (The group count should be 3 as the value has 3 different values in this group)
DD5612BB
DD56DD12
12DD5611
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor of MIS at Penn State Erie and Owner, Millet Software
Commented:
Use Distinct Count.
Or, for a more robust solution (in case the string values are not sorted) ,  group at level 2 on that string value and use a Running Total (total) with evaluate for each Group Level 2.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
That sounds like the answer

If you are oing the counts on a group be sure to use the group version of the formula

DistinctCount({Field to count}, {group field})

mlmcc
John-S PretoriusSystems Engineer

Author

Commented:
Thank you both.
John-S PretoriusSystems Engineer

Author

Commented:
My apologies that works

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial