Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
kushalthakral
Creator III
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
swuehl
MVP
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.

View solution in original post

29 Replies
sunny_talwar

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

kushalthakral
Creator III
Creator III
Author

Hi

Please find details below:

   

CampaignCustomerYears with CompanyDateRank
x101
x212
x323
x434
x545
x656
y111
y222
y333
y444
y555

 

CampaignRank (sum)
x21
y

15

Regards

Kushal Thakral

swuehl
MVP
MVP

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

Have you trued this?

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

kushalthakral
Creator III
Creator III
Author

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

sunny_talwar

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?

kushalthakral
Creator III
Creator III
Author

i will attach one more example

kushalthakral
Creator III
Creator III
Author

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

    

CampaignCustomerYears with CompanyDateRank
X1001-01-161
X2102-01-162
X3103-01-163
X4204-01-164
X5305-01-165
Y1006-01-161
Y2007-01-162
Y3108-01-163
Y4109-01-164
Y5110-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

swuehl
MVP
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