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?
Answer
Formula Results Change when Org Chart Collapsed in Lucidchart
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.
+20Hi 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?
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:
+20Thanks 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.
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.
+20I'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?
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.
+20Hi 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.
+20Following up with a solution - will you please try: =COUNTIF(DOWNSTREAMDEEP(graphnodes) node => COUNT(ASARRAY(downstream(node))) > 0)
+20Hi! 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
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to log in.
Log in to the community
A Lucid account is required to interact with the Community, and your participation is subject to the Supplemental Lucid Community Terms. You may not participate in the Community if you are under age 18. You will be redirected to the Lucid app to log in.
Log in with Lucid
or
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
