Skip to Content
0

Rank Function

Feb 21 at 10:41 AM

37

avatar image
Former Member

In terms of the Rank (Most Recent column) -I need the largest IID to list first when there is a tie in the date.

=Rank([Date];[IID];[Case Number])

I have tried

=Rank([Date]; ([IID];[Case Number]);[Case Number]) as well.

What am I doing wrong here?

ddwdk.png (8.4 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

James Barlow Feb 21 at 10:47 AM
0

Hi there,

When you say list first -- are you trying to sort so the largest IID value is first in the list?

or do you want to only show the largest IID value for a given date?

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi James,

Sorry. To clarify, in terms of the RANK the data for 7 and 8 should be swapped because IID 1565354 is more recent than 1526442.

I even have the date and the iid sorted descending, but the IID doesn't budge.

Make sense?

0
AMIT KUMAR
Feb 21 at 11:07 AM
0

follow these steps.

Sort date is in descending.After that sort IID in descending.

use formula =RunningCount(Rank([Date]))

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member
Thanks Amit, Useful but I'd need to reset the case numbers.
0

Then use case number in the formula.

=RunningCount(Rank([Date]);[Case Number]))

0
Former Member

What you are suggesting is probably more simple than doing the rank and will consider in the future but I have copied it as you have it (substituting my fields) and it still doesn't reset at change of case number.

0
avatar image
Former Member Feb 21 at 11:41 AM
0

Apparently what I thought was a DATE field, even looked like a dd/mm/yyyy date field actually has something else which skewed the sorting which is why sorting by date descending had no affect on the IID being sorted descending.

I created a variable to ensure the date was converted and went back to my original RANK

=Rank([Date];([Date];[IID]);[Case Number])

and this works now.

Share
10 |10000 characters needed characters left characters exceeded