10 Replies Latest reply on Feb 2, 2017 12:50 PM by Adam Ehrenworth

# LOD to show if value in record is appearing in more than one "group"

I have a file export with employee records based on leadership groups. In some case the same employee record (Employee ID) might appear more than once if they are in more than 1 group.

I want to have a calculated field that can represent if that Employee ID is appearing in more than one group and if so, another one that show how many groups they are in:

 Employe ID Leader Group In More Than 1 Group Group Count 12345 Group 1 Y 2 12345 Group 2 Y 2 45678 Group 3 N 1 123123 Group 1 Y 3 123123 Group 3 Y 3 123123 Group 4 Y 3 55555 Group 4 N 1

Can this be done easily? I am having trouble figuring out how and LOD might be used here....

• ###### 2. Re: LOD to show if value in record is appearing in more than one "group"

A LOD should be able to do this. Try {FIXED [EmployeeId]: COUNT(Leader Group)}

That formula should give you the count. For the Y or N try: IF SUM({FIXED [EmployeeId]: COUNT(Leader Group)}) > 1 THEN 'Y' ELSE 'N' END

None of this has been tested but it should be right.

• ###### 3. Re: LOD to show if value in record is appearing in more than one "group"

If you only need to count LG by Employee ID, you don't necessarily need to create LOD calc.

But if you need to use the field as dimensions in different calc, yes, LOD is better.

[How Many LG]

[LOD HOw many LG?]

[More than 1 ?]

if [How Many LG] =1 then "N" elseif [How Many LG] >1 then "Y" end

Thanks,

Shin

• ###### 4. Re: LOD to show if value in record is appearing in more than one "group"

Thanks -

Follow up - Is there a way to show which WWIDs are in each possible combination of groups? i.e In Group 1 and  2, In Group 2 and 3, In Group 1 and 3, In all 3 by also using some type of LOD.

Thanks

• ###### 5. Re: LOD to show if value in record is appearing in more than one "group"

Not sure but you mean this?

[Combination]

if {fixed [Employe ID] : sum(if [Leader Group]="Group 1" then 1 else 0 end)} =1 then "Group 1 " else "" end

+if {fixed [Employe ID] : sum(if [Leader Group]="Group 2" then 1 else 0 end)} =1 then "Group 2 " else "" end

+if {fixed [Employe ID] : sum(if [Leader Group]="Group 3" then 1 else 0 end)} =1 then "Group 3 " else "" end

+if {fixed [Employe ID] : sum(if [Leader Group]="Group 4" then 1 else 0 end)} =1 then "Group 4 " else "" end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: LOD to show if value in record is appearing in more than one "group"

Yes - thank you for being so responsive.

I am starting to learn the power of the LODs... appreciate the support

• ###### 7. Re: LOD to show if value in record is appearing in more than one "group"

You are welcome.

Enjoy Tableau and the forum.

Thanks,

Shin

• ###### 8. Re: LOD to show if value in record is appearing in more than one "group"

I tried to expand upon this technique and use another field for grouping but there are over 200 possible values.

When I attempted to create a formula that include that many lines I got "the expression is too complex" error message.

Any suggestions of a work around?

Regards,

• ###### 9. Re: LOD to show if value in record is appearing in more than one "group"

That's of course.

Could you attach packaged workbook with reasonable number of sample..

Thanks,

Shin

• ###### 10. Re: LOD to show if value in record is appearing in more than one "group"

There is some sensitive data... let me see if I can create dummy sample.

Appreciate you taking a look.