Hello everyone!

I am trying to analyze the degree of national and gender diversity of TMTs of german companies over a period of 10 years. The Blau Index seems to be the ideal measurement, but after trying out different possibilities, I could not find the right solution.

The formula for the Blau Index is the following:

Array

the data set:


Company_ID year gender nationality BlauGender BlauNationality
1 2010 male German
2010 female German
2011 male Austrian
2011 female German
2 2013 male Australian
2013 male German
2013 male Dutch
2014 female Dutch
2014 female Dutch
The data has to be aggregated on a company-year-level. The results of gender diversity and national diversity for every company and every year should be shown in the columns "BlauGender" and "BlauNationality".

I would appreciate any tips or suggestions on how to solve this problem. Thank you very much!