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

[swuehl]

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

[kushalthakral]

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

[swuehl]

Again:

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

[swuehl]

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

[kushalthakral]

Can you please explain the formula what is is doing

[swuehl · Accepted solution]

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.

[kushalthakral]

Thanks Swuehl

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

Regards

Kushal Thakral

[kushalthakral]

Hi Swuehl

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

Can you please help in that.

Regards

Kushal Thakral

[swuehl]

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

[kushalthakral]

Hi

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