I've read several posts from others about Count If functions and I can't quite find one that works for me. I am trying to write an expression that will tell the user their rank out of x companies. I can use a count function to determine how many companies are represented on the chart, but I cannot figure out how to get the rank portion to work. I would be fine with using a count if function so I'm only counting companies that reported a higher number than the selected company, but I can't get that to work either.
Example. Year = 2020. Employees = # of employees reported. Company = all the companies in the survey.
Company A: 5 employees
Company B: 10 employees
Company C: 15 employees
Company 😧 20 employees
Company E: 25 employees
For company C, they're the 3rd company of 5. So I want to display: "3 of 5" when company C is selected. Below is what I have. I get the "of 5" portion, but I can't get the "3" part. If I use (getfieldselections(Company)) in my equation, it just returns "1" for their rank. Any ideas? I'm sorry if this isn't explained well!
=(max({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(rank(((
Sum({1<Question={"Employees*"}>}Response)
)),Company),Company,Year)))
& ' of ' &
(count({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(((
Sum({1<Question={"Employees*"}>}Response)
)),Company,Year)))
