Accumulative array question

[ramblinrose]

I have a group of family members. For each family member there are ethnicities. I need to be able to create an array that will include all ethnicities within the family. I cannot sort the records by ethnicity because the head of the household needs to be the first record.

My records might look like this
Head of Household White
Member 1 Hispanic
Member 2 White
Member 3 Japanese

The result I would like to have is White, Hispanic, Japanese in the Group Header if possible.

I am using Version 11.5. Can someone help me with this?

Thank you.

 

[lbass]

You could try a formula like this if your result must be in the group header, adding extra clauses as needed:

nthmostfrequent(1,{table.ethnicity}, {table.family})&
(
if distinctcount({table.ethnicity},{table.family}) > 1 then
", " & nthmostfrequent(2,{table.ethnicity}, {table.family})
) &
(
if distinctcount({table.ethnicity},{table.family}) > 2 then
", "& nthmostfrequent(3,{table.ethnicity}, {table.family})
)

Another option would be to insert a crosstab in the group header.

Otherwise, you could collect the results in a variable for display in the group footer.

-LB

 

[ramblinrose]

Thank you Ibass...this solution worked.

You are GREAT!