I'm working with an organizational chart that includes multiple employees with the same name in different teams. For example:John - HR Admin John - HR Benefits Sarah - HR BenefitsI want to accurately count the total number of unique employees, regardless of their team assignments. In this case, the correct count should be 2 (John and Sarah).

Solved

How to Count Unique Employees in an Org Chart with Duplicate Names and Teams?

Forum|Forum|2 years ago
August 26, 2024
4 replies
127 views

I'm working with an organizational chart that includes multiple employees with the same name in different teams. For example:

John - HR Admin
John - HR Benefits
Sarah - HR Benefits

I want to accurately count the total number of unique employees, regardless of their team assignments. In this case, the correct count should be 2 (John and Sarah).

Best answer by Emma D

Hi @ailih.lim, it looks like this is a result of a bug on our end. However, as a workaround, you should be able to use the following formula instead:

=COUNT(UNIQUE(MAP(children.Name, x => ASSTRING(x))))

The underlying issue is with the way we handle linked data values in formulas. This workaround maps the linked data values with a conversion, thus allowing the UNIQUE function to work as expected.

Hopefully this helps, but please don’t hesitate to follow up if you have any further questions!

Emma D

Lucid support team

Hi @ailih.lim, thanks for posting. You should be able to achieve this with the UNIQUE function nested within your COUNT formula, like this:

=COUNT(UNIQUE(CHILDREN."Name"))

Hope this helps!

A

ailih.limAuthor

Hi @Emma D, I've applied the formula, but the result is incorrect.

Example: John reports to multiple supervisors and works in various Team. I want John to be represented as one headcount in my Lucidchart Analysis. Please refer to the google sheet screenshot and create another formula for the scenario below.

Emma D

Lucid support team

My apologies- it looks like the aforementioned formula only works when you reference a custom data property on your org chart shapes. I’ve reached out internally for possible alternatives and will come back to you shortly.

Emma DAnswer

Lucid support team

Hi @ailih.lim, it looks like this is a result of a bug on our end. However, as a workaround, you should be able to use the following formula instead:

=COUNT(UNIQUE(MAP(children.Name, x => ASSTRING(x))))

The underlying issue is with the way we handle linked data values in formulas. This workaround maps the linked data values with a conversion, thus allowing the UNIQUE function to work as expected.

Hopefully this helps, but please don’t hesitate to follow up if you have any further questions!

Create an account in the community

Log in to the community