Counting rank of 1

[itcapability]

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

[OmarBenSalem]

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]

 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.

Adrian

[OmarBenSalem · Accepted solution]

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 :

[itcapability]

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