Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Creator III

## Rank function with multiple dimensions

Hi All

I have some queries regarding use of rank function

i have data like below

CampaignCustomerYears with CompanyDateRank 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

CampaignRank

Regards

Kushal Thakral

1 Solution

Accepted Solutions
MVP

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.

29 Replies
MVP

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

Creator III
Author

Hi

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

MVP
Campaign (Sqr(COUNT(DISTINCT Customer)) +Count(DISTINCT Customer))/2
x21
y15
MVP

Have you trued this?

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

Creator III
Author

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

MVP

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?

Creator III
Author

i will attach one more example

Creator III
Author

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

MVP

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)
x11
x22
x33
x44
x55
x66
y11
y22
y33
y44
y55
Community Browser