rankings in a text box and straight table

[#1]

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

[sunny_talwar · Accepted solution]

1) Try this for straight table expression

=Rank(Count({<Gender={'Female'}, Company>} newkey)/Count({<Company>}newkey)) * Avg(1)

2) Try this for text box expression

=Aggr(Rank(Count({<Gender={'Female'}, Company>} newkey)/Count({<Company>}newkey)), Company)

[#3]

Hi Sunny, 

Straight table:

What worked:

When selecting a company from the straight table, it kept the original ranking and did not revert to 1.

What did not work:

The rankings stay fixed and do not respond to changes to the Year and Month listboxes.

Company x might be ranked 2 in 2015 but is ranked 3 in 2016. With your expression, it stays fixed at 2 regardless of the listbox selections.

Text box:

The expression did not work and brought back a hyphen.

[jonathandienst]

You conclusions about not responding to dates look inconsistent with what I would expect from Sunny's first expression. We may need more information about the data model in your application to help resolve your issue. A small sample qvw illustrating the problem, including the chart and the text box, would help.

[#5]

Hi Jonathan,

The straight table responds and correctly ranks the companies using my expression. If I select a Year or Month from a list box, the straight table updates the rankings accordingly.

=rank(num(Count({<Gender={'Female'}>}newkey))/Count(newkey),'#,##0.0%')

However, if I click on one of the companies, the ranking reverts to 1. Sunny's code solves for this. However, her code is not responsive to the list boxes (Year and Month list boxes respectively).

In essence, it is very similar to this:

=rank(num(Count({1<Gender={'Female'}>}newkey))/Count(newkey),'#,##0.0%')

With the exception that Sunny's rankings do not revert to 1 when the company is selected.

Going forward, I will look at providing sample qvw's but am hoping this can be resolved without this. This is my first question on the forum and I am still getting used to it.

[sunny_talwar]

Did you select company before selecting a year or month? I think it should work based on your selection in year and month. Would you be able to share a sample where it isn't working?

[sunny_talwar]

His code. I am male my friend

[#8]

My bad. Didn't look at your picture. Just made an assumption Sorry.

[#9]

Hi Sunny,

I've tried your expressions at home with my own dummy dataset (no data models, fact tables etc), just a plain old excel spreadsheet. Your expressions work with the dummy dataset.

I'll have to dig a bit deeper to see what's happening with the model.

Kind regards

Declan

[sunny_talwar]