Hi
Hope you are well.
I have a straight table which ranks each company based on the proportion of their new recruits that are female.
I have 3 list boxes - Year, Month, Division.
company | proportion female | ranking |
x | 75% | 3 |
y | 85% | 2 |
z | 90% | 1 |
Firstly, this is the code I use to rank the companies:
=rank(num(Count({<Gender={'Female'}>}newkey))/Count(newkey),'#,##0.0%')
I have 2 questions:
First question:
The rankings are correctly ordered when changing the year or months. However, when selecting a specific company, let's say x, the ranking reverts to 1 (but overall it is ranked 3). I am aware of the $ , 1-$ and 1. However, if I use {1}, it will then ignore any selections I make with ALL the list boxes. I want it to respect the Year and Month list boxes but still be able to give me the company ranking when it is selected.
Second question:
Let us assume I selected the year 2015 and the month as July. Let us assume, to keep things simple, that the ranking for company Y was 2 (like the straight table).
I’d like to write a code but in a text box that will bring back 2 if I select that specific company (Y) from the list box or straight table.
Assuming I selected the year 2015, month, July, and the company Y was ranked 2 in the straight table, I’d like to write a code in a text box which will tell me that the ranking of the company is 2. If I select the company, the text box must not revert to 1 either. It must keep the ranking (2) in this case.
I’m struggling to do this ranking in a text box. Please can you help?
Kind regards
D
