LAC - Workaround to use if function with LAC

I have the following hypothetical dataset. I need to find the number of unique Teams that contain only ‘Woman’ Employees, the number of unique Teams that contain only ‘Man’ Employees and the number of unique Teams that contain both.

Employee ID. Gender Team

1001 Woman. A
1002 Man. A
1003 Woman. B

I’ve tried the following calculated fields:
Teams that contain only ‘Woman’ Employees

  • Distinct_countif (Team, Gender, “Woman”)
  • Distinct_countIf(Team, contains(Gender, ‘Woman’) AND notIn(Gender, [‘Man’]))

These are not giving me the right answer. Can you suggest how I can achieve this?


Hello @Drish, I think we can definitely make this work! First, I’d say lets get the number of distinct employee genders per team to exclude any teams that contain both.

gendersPerTeam = distinctCountOver({Gender}, [{Team}, PRE_AGG)

Now only return Teams that have a value of 1:

teamsWithOneGender = ifelse({gendersPerTeam} = 1, {Team}, NULL)

Now we can check for the distinct count of teams with 1 or the other:

teamsWithMen = ifelse({Gender} = "Man", {teamsWithOneGender}, NULL)
teamsWithWomen = ifelse({Gender} = "Woman", {teamsWithOneGender}, NULL)

Now you can run distinct count on each function! It may require a distinctCountOver because of the previous syntax, so I’ll type it out:

countOfTeamsWithMen = distinctCountOver({teamsWithMen}, [], PRE_AGG)
countOfTeamsWithWomen = distinctCountOver({teamsWithWomen}, [], PRE_AGG)

I’ll mark this as the solution, but if you have any follow-up questions, let me know!

1 Like