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]

Maybe

Only(TOTAL<[Campaign Name], Customer> Aggr(Num(Rank(-Only([Years with AIB]),4,1)),[Campaign Name],Customer))

or

=Only(Aggr(NODISTINCT Num(Rank(-Only([Years with AIB]),4,1)),[Campaign Name],Customer))

[kushalthakral]

I have tried your solution but it is assigning same rank to all other customers

like

A 1

B 1

C 1

but the result should be like

A 1

B 2

C 3

Thanks

Kushal

[swuehl]

Please post a small sample application.

As far as I understood, A,B,C are campaign names, and you don't want to rank across campaigns.

[kushalthakral]

For this example i have taken A, B, C as Employees just to elaborate the output of your solution

[swuehl]

Can't reproduce it here.

Please post a small sample QVW.

[kushalthakral]

okk i will post in some time

[Saravanan_Desingh]

To add to swuehl formula, we can also use the below:

(Count(DISTINCT Customer)/2)*(2*FirstSortedValue(Customer,Customer)+(Count(DISTINCT Customer)-1)*(FirstSortedValue(Customer,Customer,2)-FirstSortedValue(Customer,Customer)))

Here, in your example the sequence is like 1,2,3,4..

But if you have sequence like 1,3,5,7,9,11 the above formula can be used..

[kushalthakral]

Hi Swuehl

i am attaching the sample QVW file , have a look on it

Actually i want a Rank column for individual Campaigns based on Years with Company and T

and for a new campaign rank should starts with 1

as of now it is showing a continuous rank

Thanks

Kushal

[swuehl]

Maybe just remove the TOTAL qualifier from the Rank() function

=Aggr(Num(Rank( -([Years with Company]),4)),Campaign,T)

[kushalthakral]

Thanks a lot Swuehl

It was a simple thing, i dont know how i missed that

Thanks

Kushal