10 Replies Latest reply on Jan 16, 2013 4:25 PM by d.bb

# Rank Tie with Suffix

Am using the following to create a ranking measure, which works the way I need it to:

IF ISNULL([Measure]) THEN

NULL

ELSE

IIF([Measure] == LOOKUP([Measure], -1), PREVIOUS_VALUE(1), INDEX(), 1)

END

So, with a column having the values of 10, 30, 30, 30, 45, 80, the above ranks them thus, while maintaining index position for the non-ties:

1

2

2

2

5

6

However, I would like to add a tie suffix to these values, as follows:

1

2t

2t

2t

5

6

Any push in the right direction would be greatly appreciated.

-D

• ###### 1. Re: Rank Tie with Suffix

Something like:

IF [Measure] == LOOKUP([Measure], -1) OR [Measure] == LOOKUP([Measure], 1)

THEN

STR(  IIF([Measure] == LOOKUP([Measure], -1), PREVIOUS_VALUE(1), INDEX(), 1)) + "t"

ELSE

IIF([Measure] == LOOKUP([Measure], -1), PREVIOUS_VALUE(1), INDEX(), 1)

END

You didn't provide a workbook, so I just did this in my head without testing . But it meets your requirement of a push in the right direction.

--Shawn

• ###### 2. Re: Rank Tie with Suffix

The only thing you may be missing Shawn is making the else a string as well - I couldn't work out the brackets...

• ###### 3. Re: Rank Tie with Suffix

Now you're going to make me actually test it. Or I guess I could let him test it and let us know.

--Shawn

• ###### 4. Re: Rank Tie with Suffix

Thanks for the (VERY rapid response; awesome).

Tried STR() as indicated (and with the ELSE block, too).  Causes some IIF issues (bool, str, int) type issues, so I tried str() on the individual parameters until that went away, etc.

But I can't get around PREVIOUS_VALUE(1), which throws a Type Mismatch.  It's defined as an integer, and apparently doesn't like being type recast...

-D

• ###### 5. Re: Rank Tie with Suffix

Throw up a workbook with a bit of dummy data and I'll see if I can't work it out for you.

--Shawn

• ###### 6. Re: Rank Tie with Suffix

This may be solved with previous_value("1") to cast the rank as a string straight away

• ###### 7. Re: Rank Tie with Suffix

So using Alex's suggestion, I'm half-way there, but I'm getting a very unexpected behavior:

The t's are getting doubled, which doesn't really make sense to me. I'm attaching the workbook in case someone else has any ideas.

--Shawn

• ###### 8. Re: Rank Tie with Suffix

Bring the 't' inside - you were adding t the previous value which may already have a t on it:

IF

SUM([Order Quantity])==LOOKUP(SUM([Order Quantity]),-1) OR SUM([Order Quantity])==LOOKUP(SUM([Order Quantity]),1)

THEN

IIF(SUM([Order Quantity])==LOOKUP(SUM([Order Quantity]),-1),PREVIOUS_VALUE("1"), STR(INDEX())+"t","1")

ELSE

STR(INDEX())

END

I got rid of the last iif - can't see what that was doing as we had already checked?

• ###### 9. Re: Rank Tie with Suffix

Cool. I get it. Yep simpler is better. Interesting syntax problem.

--Shawn

• ###### 10. Re: Rank Tie with Suffix

That did it.  I got the multiple "t" suffix, too.  Saw why it was happening, but I took a different approach to deal with them, but it ended up not flagging the first tie in a series, if the immediately previous series was also a tie.

But Alex's approach was much more compact and elegant, so this did it:

IF

[Measure]==LOOKUP([Measure],-1) OR [Measure]==LOOKUP([Measure],1)

THEN

IIF([Measure]==LOOKUP([Measure],-1),PREVIOUS_VALUE("1"), STR(INDEX())+"t","1")

ELSE

STR(INDEX())

END

Thanks, all!

-D