topic Rank function with multiple dimensions in QlikView

Rank function with multiple dimensions

kushalthakral — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have some queries regarding use of rank function

i have data like below

Campaign	Customer	Years with Company	Date	Rank	Header 6

I am calculating Rank using below formula

Aggr(Num(Rank(Total -[Years with Company],4)),Customer,[Campaign],[Years with Company],[Date])

Now i want to use the same in the below table but not getting proper output can any have a solution to this

Campaign	Rank

Regards

Kushal Thakral

Re: Rank function with multiple dimensions

sunny_talwar — Mon, 13 Jun 2016 15:42:01 GMT

Would you be able to provide a sample with expected output you would want to see?

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 15:51:51 GMT

Please find details below:

Campaign	Customer	Years with Company	Date	Rank
x	1	0	1
x	2	1	2
x	3	2	3
x	4	3	4
x	5	4	5
x	6	5	6
y	1	1	1
y	2	2	2
y	3	3	3
y	4	4	4
y	5	5	5

Campaign	Rank (sum)
x	21
y	15

Regards

Kushal Thakral

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 16:02:18 GMT

Campaign	(Sqr(COUNT(DISTINCT Customer)) +Count(DISTINCT Customer))/2
x	21
y	15

Re: Rank function with multiple dimensions

sunny_talwar — Mon, 13 Jun 2016 16:03:03 GMT

Have you trued this?

Sum(Aggr(Num(Rank(Total -[Years with Company],4)),Customer,[Campaign],[Years with Company],[Date]))

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 18:50:25 GMT

i have tried to this but because of Total i am not getting correct results

Re: Rank function with multiple dimensions

sunny_talwar — Mon, 13 Jun 2016 18:57:08 GMT

Have you looked at Stefan's suggestion also? If non of our suggestion works, would you be able to provide a sample to look at?

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 19:06:36 GMT

i will attach one more example

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 19:17:09 GMT

i want to calculate rank based on years with Company, Customer, Campaign like below example

Campaign	Customer	Years with Company	Date	Rank
X	1	0	01-01-16	1
X	2	1	02-01-16	2
X	3	1	03-01-16	3
X	4	2	04-01-16	4
X	5	3	05-01-16	5
Y	1	0	06-01-16	1
Y	2	0	07-01-16	2
Y	3	1	08-01-16	3
Y	4	1	09-01-16	4
Y	5	1	10-01-16	5

if i use total it will calculate based on entire data set but it has to reset according to Campaign means when new campaign comes there it should again start with 1

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 19:36:07 GMT

so it's all about calculating the rank(), not the sum?

Try

=Rank( -Only(YearsWithCompany), 4,1)

in a context with dimensions Campaign, Customer.

Campaign	Customer	Rank(-Only([Years with Company]),4,1)
x	1	1
x	2	2
x	3	3
x	4	4
x	5	5
x	6	6
y	1	1
y	2	2
y	3	3
y	4	4
y	5	5

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 19:40:06 GMT

If you need to add the Date information, use an expression:

Campaign	Customer	Only(Date)	Num(Rank(-Only([Years with Company]),4,1))
X	1	01-01-16	1
X	2	02-01-16	2
X	3	03-01-16	3
X	4	04-01-16	4
X	5	05-01-16	5
Y	1	06-01-16	1
Y	2	07-01-16	2
Y	3	08-01-16	3
Y	4	09-01-16	4
Y	5	10-01-16	5

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 19:52:28 GMT

But at the end i want to show this info as below:

sum of all the ranks of that particular campaign

Campaign	Sum of Rank
X	15
Y	15

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 20:01:15 GMT

Again:

Campaign	=(sqr(Count(DISTINCT Customer))+Count(DISTINCT Customer))/2
X	15
Y	15

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 20:02:54 GMT

Or if you insist on the overhead of advanced aggregation and ranking:

Sum(Aggr(Num(Rank(-Only([Years with Company]),4,1)),Campaign,Customer))

Campaign	Sum(Aggr(Num(Rank(-Only([Years with Company]),4,1)),Campaign,Customer))	=(sqr(Count(DISTINCT Customer))+Count(DISTINCT Customer))/2
X	15	15
Y	15	15

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 20:03:14 GMT

Can you please explain the formula what is is doing

Re: Rank function with multiple dimensions

swuehl — Mon, 13 Jun 2016 20:08:22 GMT

If you have N lines showing the rank from 1 to N, the formula will calculate the sum of these values 1 to N.

=(Sqr(N)+N)/2

That's just maths, not depending on the rank() function.

Count(DISTINCT Customer) is giving the number of these lines (N) per campaign.

Re: Rank function with multiple dimensions

kushalthakral — Mon, 13 Jun 2016 22:14:38 GMT

Thanks Swuehl

for now its working i will revert back if i need some more information on the same

Regards

Kushal Thakral

Re: Rank function with multiple dimensions

kushalthakral — Tue, 14 Jun 2016 09:37:46 GMT

Hi Swuehl

It is not working if we have multiple customer on two different dates

Can you please help in that.

Regards

Kushal Thakral

Re: Rank function with multiple dimensions

swuehl — Tue, 14 Jun 2016 09:40:24 GMT

Can you post a sample input table and your requested output?

Re: Rank function with multiple dimensions

kushalthakral — Tue, 14 Jun 2016 09:51:31 GMT

Please look at the below table

Campaign Name	Customer	Years with Company	Lead Date	=Aggr(Num(Rank(-Only([Years with AIB]),4,1)),[Campaign Name],Customer)
				1
A	6988386	7	42217	1
B	6988386	7	42238	1
C	6988386	7	42271	-
C	6988386	7	42272	1