How to do set analysis using a data field's value?

[#1]

Ok, I've got a Set Analysis issue I'm trying to work out.

I want to build a list of all departments an employee has worked for that *aren't* his/her 'home' department.  I've nearly gotten the set analysis expression worked out, but I've run into a brick wall.

This one does what I want to, except that it forces the excluded department code value to be 1126.

Concat({$<ActualDepartmentCode-={"1126"}>} DISTINCT $(DepartmentNumberAndName), ', ')

Unfortunately, not every employee record has a HomeDepartmentCode value of 1126.  For example, Employee 10393 has a home department of 1113.

What do I use in place of the ... below to get it to evaluate the currently associated HomeDepartmentCode value so it will be correct for *all* employees?

Concat({$<ActualDepartmentCode-={...}>} DISTINCT $(DepartmentNumberAndName), ', ')

[Jason_Michaelides · Accepted solution]

I think

Concat({$<ActualDepartmentCode-={"=$(Only(HomeDepartmentCode))}>} DISTINCT $(DepartmentNumberAndName), ', ')

is the right syntax, HOWEVER....it sounds like you are hoping to have a list of different employees, each of which may have a different HomeDepartmentCode. Set analysis only evaluates the set ONCE per object, so in this case it wouldn't work. If Employee is the dimension them you might instead try:

Concat(DISTINCT If(DeptCode<>HomeCode,DepartmentNumberAndName),',')

Hope this helps,

Jason

[#3]

Ok, that gave me what I wanted.  Thanks!

A quick followup question, though:

I'm getting that list as my expression in a Pivot Table.  I want to make it easier to read the results.  Is there any way to make each of the results show up on its own line?  I've tried passing CR, LF, and CRLF characters as the delimiter, but it doesn't seem to work.  Failing that, is there any way, once I have the Concat'd list, to split it back up so that they each get their own cell in the pivot chart?

[Jason_Michaelides]

Have you tried Chr(10) as the delimiter?

[#5]

Would have sworn I had, but it just worked.  Thanks!

Edit:

Unfortunately, that ends up making things worse though because the cell-height doesn't seem to have an option for 'auto-expand to fit'.  Oh well.