Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
itcapability
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,

Adrian

1 Solution

Accepted Solutions
OmarBenSalem
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:

Capture.PNG

 

See if I select Provider A :

Capture.PNG

4 Replies
OmarBenSalem
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

itcapability
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 - SpeedProvider AProvider BProvider CProvider DProvider E
AB1108642
AB2246810
AB3108462
AB4210864
AB5421086

 

Postcode District - RankProvider AProvider BProvider CProvider DProvider E
AB112345
AB254321
AB312435
AB451234
AB545123

 

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.

 

Adrian

OmarBenSalem
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:

Capture.PNG

 

See if I select Provider A :

Capture.PNG

itcapability
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.