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

[#2]

I think the Qlikview version has something to do with it. I've got an older version at home than at work.

Your expressions work in a pivot table but not the straight table (Qlikview version 11.20)

However, at home, the same expressions work in a straight table (not at home now so not sure what version but it's an older one).

Also your expressions work in the text box, regardless of version.

Thanks so much

[sunny_talwar]

Try this in the straight table also:

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

[#4]

Hi Sunny,

I have another question. What I did below worked on a pivot table but I can't get it to work on a text box.

Let us say company X is ranked 6 on a metric, and 4 on another metric. If we add the rankings for company X, we get 10.

Let us assume company Y is ranked 5 on a metric and 8 on another metric. If we add the rankings for company Y, we get 13.

I created a variable to rank the companies (from lowest to highest) in a pivot table using the following code:

=aggr(rank(-($(vOverallScore))),Company)

To get vOverallScore, I simply took the code for the first metric and added the plus sign and then added the code for the 2nd metric.

vOverallScore (variable created):

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

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

Then in the pivot:

=aggr(rank(-($(vOverallScore))),Company)

The pivot shows correct rankings. However, it becomes vulnerable to reverting to a ranking of 1 when you select the company. Your code is robust to changes but as soon as I put the + sign, it becomes vulnerable to selections.

Perhaps there is another way to do it? and to get it work in a text box?

Kind regards
D

[sunny_talwar]

Can you try this:

=Only({<Company>} Aggr(Rank(-($(vOverallScore))),Company))

[#6]

Using your code, It brings back 1 for the text box and also 1 for the pivot table

The pivot table shows the correct rankings but if you select the company, it reverts to 1.

[sunny_talwar]

Would it be possible to share a sample?

[#8]

I think we might have to do that. I'll try and provide one tomorrow.

[#9]

I think I got it!  Made up a sample qvw:

=aggr(rank((rank(sum({<Company>}[Product A]),Company) + rank(sum({<Company>}[Product B]),Company))),Company)

Will share tomorrow. Can't share on my home PC. I've got personal edition at home.

Kind regards

Declan

[#10]

Hi

Sorry for the late reply. But better late than never? Please let me know if you are able to view the attachment.

[sunny_talwar]

Is this your achieve solution or are you still looking for help?