Solved

Formula Results Change when Org Chart Collapsed in Lucidchart

Forum|Forum|3 years ago
April 18, 2023
9 replies
160 views

Hi - I have an org chart where I am trying to calculate the number of people by their role. When the org chart is expanded the formula works as expected but when the chart is collapsed it only counts the visible shapes. How can I get it to count all shapes whether they are visible or not?

Best answer by Micah

Thanks for your response and describing what formulas you're currently using. I recommend taking a look at this community post which addresses a very similar question. Because you're displaying these numbers by role in a shape that is separate from your org chart, you'll need to utilize two formulas - one to sum the number of employees by role type using a combination of DOWNSTREEMDEEP and graphnodes (which will allow the calculation to perform correctly even when the org chart is collapsed) and a second to pull those sums into your separate table to display them.

In the linked example the user is summing direct reports but you can replace the custom field in that instance - "Direct Report Count" - with yours that contains the employee role. Please let me know if you have any additional questions and refer to our Formulas documentation for any specific formula references.

Micah

Lucid community team

Hi there thanks for posting in the Lucid community! Do you mind clarifying the formula that you're using to calculate the number of employees in each role as well as the shape in which you are performing this calculation? Have you added it to an employee tile or is it on an separate object from your org chart?

Cummings, BAuthor

I've used COUNTIF(CHILDREN THIS.XXX) and COUNTIF(DOWNSTREAMDEEP THIS.XXX) as my formulas. When I have the org chart fully expanded the numbers calculate like this:

However when I have the org chart collapsed like this:

The numbers calculate like this and I would like them to still appear as they do in the top chart:

MicahAnswer

Lucid community team

Thanks for your response and describing what formulas you're currently using. I recommend taking a look at this community post which addresses a very similar question. Because you're displaying these numbers by role in a shape that is separate from your org chart, you'll need to utilize two formulas - one to sum the number of employees by role type using a combination of DOWNSTREEMDEEP and graphnodes (which will allow the calculation to perform correctly even when the org chart is collapsed) and a second to pull those sums into your separate table to display them.

In the linked example the user is summing direct reports but you can replace the custom field in that instance - "Direct Report Count" - with yours that contains the employee role. Please let me know if you have any additional questions and refer to our Formulas documentation for any specific formula references.

Cummings, BAuthor

Thank you! That worked but then I ran into another issue...

This formula is working now: =COUNTIF(DOWNSTREAMDEEP(graphnodes)THIS."Role"='XX')
This formula is still not working when collapsed: =COUNTIF(DOWNSTREAMDEEP(graphnodes)THIS."Direct Reports">0)

Role is a field I imported and Direct Reports is a field that's a function of the Org Chart. I'm really just looking to filter on who is a supervisor vs. not so I don't have to use that exact formula format.

Micah

Lucid community team

I'm glad to hear that first formula is working as expected! Regarding your other question - can you please clarify what you mean by "filter on who is a supervisor vs. not"? Are you hoping to visually display on your org chart how many reports any one individual has? Or are you hoping to understand if someone fulfills a certain role and is also (or is not) a supervisor?

Cummings, BAuthor

I'm trying to put a similar view together as the above that says how many people are managers/have direct reports vs. those that are individual contributors.

=COUNTIF(DOWNSTREAMDEEP(graphnodes)THIS."Direct Reports">0) works when the org chart isn't collapsed but unlike the other formulas it doesn't work when the org chart is collapsed.

Micah

Lucid community team

Hi thanks for getting back to me and clarifying what you're hoping to do. I've been able to reproduce the issue with this formula not functioning properly when the org chart is collapsed and am currently internally investigating the modification needed to prevent this. I'll update this thread when I hear more! Thank you for your patience.

Micah

Lucid community team

Following up with a solution - will you please try: =COUNTIF(DOWNSTREAMDEEP(graphnodes) node => COUNT(ASARRAY(downstream(node))) > 0)

This formula refers to the data itself rather than the shapes on the document which should allow you to calculate the number of supervisors even when the org chart is collapsed. Please give this a try and let me know if you have additional questions!

Micah

Lucid community team

Hi! Jumping in here to share about Lucid’s Data Trusted User Group. This group is made up of knowledgeable users who regularly analyze and visualize data in their work. As part of this group, you’ll have the opportunity to connect directly with Lucid’s product team, gain early access to beta features, and share insights about your needs, shaping the future of Lucid’s data solutions 🎉 You can learn more and request to join here. Thanks!

Create an account in the community

Log in to the community