0

# Rank Function

Feb 21 at 10:41 AM

37

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)

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
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?

AMIT KUMAR
Feb 21 at 11:07 AM
0

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

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

Show 3 Share
Former Member
Thanks Amit, Useful but I'd need to reset the case numbers.

Then use case number in the formula.

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

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.

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