Rank function with multiple dimensions

[kushalthakral]

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

[sunny_talwar]

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

[kushalthakral]

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

[swuehl]

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

[sunny_talwar]

Have you trued this?

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

[kushalthakral]

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]

i will attach one more example

[kushalthakral]

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

[swuehl]

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