topic Re: rankings in a text box and straight table in QlikView

rankings in a text box and straight table

Mon, 21 Nov 2016 10:04:17 GMT

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

Re: rankings in a text box and straight table

sunny_talwar — Mon, 21 Nov 2016 10:29:22 GMT

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)

Re: rankings in a text box and straight table

Mon, 21 Nov 2016 11:15:04 GMT

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.

Re: rankings in a text box and straight table

jonathandienst — Mon, 21 Nov 2016 11:39:49 GMT

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.

Re: rankings in a text box and straight table

Mon, 21 Nov 2016 11:48:19 GMT

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.

Re: rankings in a text box and straight table

sunny_talwar — Mon, 21 Nov 2016 11:55:26 GMT

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?

Re: rankings in a text box and straight table

sunny_talwar — Mon, 21 Nov 2016 11:56:35 GMT

His code. I am male my friend

Re: rankings in a text box and straight table

Mon, 21 Nov 2016 11:59:15 GMT

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

Re: rankings in a text box and straight table

Mon, 21 Nov 2016 17:46:00 GMT

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

Re: rankings in a text box and straight table

sunny_talwar — Tue, 22 Nov 2016 01:49:57 GMT

Re: rankings in a text box and straight table

Tue, 22 Nov 2016 06:19:49 GMT

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

Re: rankings in a text box and straight table

sunny_talwar — Tue, 22 Nov 2016 10:23:57 GMT

Try this in the straight table also:

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

Re: rankings in a text box and straight table

Mon, 28 Nov 2016 12:14:06 GMT

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

Re: rankings in a text box and straight table

sunny_talwar — Mon, 28 Nov 2016 12:26:59 GMT

Can you try this:

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

Re: rankings in a text box and straight table

Mon, 28 Nov 2016 13:03:07 GMT

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.

Re: rankings in a text box and straight table

sunny_talwar — Mon, 28 Nov 2016 13:04:59 GMT

Would it be possible to share a sample?

Re: rankings in a text box and straight table

Mon, 28 Nov 2016 13:05:51 GMT

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

Re: rankings in a text box and straight table

Mon, 28 Nov 2016 17:54:32 GMT

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

Re: rankings in a text box and straight table

Thu, 08 Dec 2016 13:28:20 GMT

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

Re: rankings in a text box and straight table

sunny_talwar — Thu, 08 Dec 2016 13:53:15 GMT

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