Calculating team size using iterative macro

[Dheeru28]

Hi All,

I have this workflow with an iterative macro. I had downloaded the macro from some other post on the community. What I want to get as output is the team size for all the employees who are managers. I have attached a sample workflow with dummy data.

In the real data (the one I have), the results are correct for most employees, but incorrect for managers with very large teams. I'm unable to find what is the problem.

Can someone please help out?

[Clifford_Coon]

Hi @Dheeru28 ,

Have you tried changing the maximum number of iterations?

This could cause it to exit early on large groups.

 

[Dheeru28]

Hi @Clifford_Coon 

Thanks for your reply. I changed the max no of iterations to 250. Still not working.

If you have any other thoughts please let me know.

[Christina_H]

The problem is that you're losing data by joining the iteration input to itself, and losing anyone managed by people who have already left the macro.  The macro is also a lot more complicated than it needs to be.  I've removed tools that are unnecessary and added a new input that doesn't change with the different iterations.

[Dheeru28]

@Christina_H 

Thank you. It's simpler now with less tools.

But still the same issue. When I calculate in excel, it's correct. But on Alteryx, results are incorrect for quite a good number of managers.

The total no of rows (in my data) are close to 3 lakhs.

[Christina_H]

@Dheeru28 

OK, are you able to share some sample data where it doesn't work correctly?  I've made it work for the data you've provided, but I can't easily determine what's going wrong with different data.

[Dheeru28]

Yes, in my actual data I was using a filter before the macro.

Here, I've done the same and now results are incorrect.

Please see the attached workflow. It has 2 workflows, one without filters and one with filter. The macro used is the one you gave (simplified one).

Can you suggest some way to resolve this?

[Christina_H]

By filtering before the macro, it's calculating group sizes based on the filtered data.  What is the correct outcome in this case?  Since you're filtering to group A and all managers are in group A, it is correctly calculating the number of group A employees for each manager.  What should it be showing instead?

[Dheeru28]

There is a rule: Any person from Group B shouldn't be included in the calculations for team size.

(For my data) In excel, I use formula with ifs & sumifs and the Group column has 1 & 0 instead of A & B. So, employee_number that don't need to be counted are automatically excluded as they have 0 value.

Here, I think putting a filter is not working. So, is there any other way we can get the same outcome as in excel?

[Christina_H]

I think that's what it's doing?

Employee 4963 is managing two employees, one A and one B.  Team size is returned as 1.

Employee 4093 manages Bs only, so isn't shown as a manager here.

Employee 5691 manages a large mixed team, and the size is correctly shown as the number of A employees (9).