# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## Counting rank of 1

Hi All,

Hope you can help, I'm still pretty new to Qlik and have run into a brick wall.

I have a set analysis that calculates the rank between 5 providers.  This works fine in a table/pivot table and I use it to set the background colour of a cell based upon the ranking (1st = green etc).  I now want to 'count' how many times a provider is, say, 1st or 2nd - so that I can create a KPI  (or table): "How many times last month was provider A 1st or 2nd".

The dimensions are Provider and Postcode District, the measure is the rank function:

rank(avg({<[test_date.autoCalendar.MonthsAgo]={"=1"},postcode_district=>}[speed])

The question I'm trying to answer is

"Last month provider A was 1st or 2nd in X number of postcode districts. Provider B was 1st or 2nd in Y number of postcodes".

I have googled this and the limited number of examples I've found aren't working for me.

Hope that's not gibberish ??!!

Thanks,

Labels (5)

• ### Set Analysis

1 Solution

Accepted Solutions
Esteemed Contributor II

## Re: Counting rank of 1

In the script, do as follow:

CrossTable (Provider,FakeData) load * Inline [
Postcode District - Speed, Provider A, Provider B, Provider C, Provider D, Provider E
AB1,10,8 ,6 ,4 ,2
AB2,2 ,4 ,6 ,8 ,10
AB3,10,8 ,4 ,6 ,2
AB4,2 ,10 ,8 ,6 ,4
AB5,4 ,2 ,10,8 ,6
];

Than, in ur sheet, do as follow:

as dimension: Provider

as a measure:

max( aggr(rangesum(above(if(aggr(rank(Sum({<Provider>}FakeData),Provider),[Postcode District - Speed],Provider)=1,1),0,RowNo())),Provider,[Postcode District - Speed]))
/ count(distinct total {<Provider,[Postcode District - Speed]>} [Postcode District - Speed])

Result:

See if I select Provider A :

4 Replies
Esteemed Contributor II

## Re: Counting rank of 1

For this type of questions; please, and to assure someone will "dare" to reply, try to come up with some data.

even 10 lines of data with what u have and what u want to achieve.

It's much easier to test things out when trying to respond to someone; don't forget that the majority of us are at work and still try to answer ur questions.. (we don not have much free time to understand/create data based on question/ reorgnize it/come up with a solution/test it / share)

Hope u'd understand

Highlighted
New Contributor

## Re: Counting rank of 1

Hi,

I can't share real data I'm afraid.  I have created some fake data which I hope will demonstrated what I am trying to do.

Below are 2 tables; both have Provider and Postcode District as dimensions, with average speed and rank as measures.

 Postcode District - Speed Provider A Provider B Provider C Provider D Provider E AB1 10 8 6 4 2 AB2 2 4 6 8 10 AB3 10 8 4 6 2 AB4 2 10 8 6 4 AB5 4 2 10 8 6

 Postcode District - Rank Provider A Provider B Provider C Provider D Provider E AB1 1 2 3 4 5 AB2 5 4 3 2 1 AB3 1 2 4 3 5 AB4 5 1 2 3 4 AB5 4 5 1 2 3

Provider A has the fastest speed, and a rank of 1 in 2 postcodes; AB1 & AB3, therefore a KPI could be Provider A 40% number 1 (2 divided by 5).

My rank function [rank(avg({<[test_date.autoCalendar.MonthsAgo]={"=1"}>}speed))] will work for the second table. What I can't get to work is to count up all the 1st for every postcode.

I have tried aggr(if & if(aggr and couldn't get it to work.  I've thought about trying a \$expansion but can't seem to get the syntax correct.

Hope that's OK ?  Any suggestions gratefully received.

Esteemed Contributor II

## Re: Counting rank of 1

In the script, do as follow:

CrossTable (Provider,FakeData) load * Inline [
Postcode District - Speed, Provider A, Provider B, Provider C, Provider D, Provider E
AB1,10,8 ,6 ,4 ,2
AB2,2 ,4 ,6 ,8 ,10
AB3,10,8 ,4 ,6 ,2
AB4,2 ,10 ,8 ,6 ,4
AB5,4 ,2 ,10,8 ,6
];

Than, in ur sheet, do as follow:

as dimension: Provider

as a measure:

max( aggr(rangesum(above(if(aggr(rank(Sum({<Provider>}FakeData),Provider),[Postcode District - Speed],Provider)=1,1),0,RowNo())),Provider,[Postcode District - Speed]))
/ count(distinct total {<Provider,[Postcode District - Speed]>} [Postcode District - Speed])

Result:

See if I select Provider A :

New Contributor

## Re: Counting rank of 1

Thanks for the help, works perfectly. sorry it took so long to come back, we've had issues with our Qlik server.