Search code examples
business-intelligencespotfire

How to rank values from asc/descending?

Struggling to find rank values from highest to lowest, please see attached example of what I'm trying to achieve.

My current custom expression is:

Sum([ViolationAmt])

I have tried this:

Sum([ViolationAmt]) over Rank([ViolationAmt])

I've played around with the rank expressions however unable to implement...would be very grateful for some help.

Spotfire Rank Example

enter image description here

Solution

  • I need to make a lot of assumptions here because I don't know anything about your data set or really what your end goal is, so please comment back and/or provide more info in your question if I am off base.

    the first assumption is that each row in your dataset represents one, for simplicity, [AccountID] with a [ViolationAmt]. I'm also guessing you want to show the top N accounts with the highest violations in a table, since that's what you've shown here.

    so it sounds like you are going to need two calculated columns: one for getting the total [ViolationAmt] per account, and then another to rank them.

    for the first, create a column called [TotalViolationAmt] or somesuch and use:

    Sum([ViolationAmt]) OVER ([AccountID])

    for the second:

    Rank([TotalViolationAmt])

    it will be useful to read the documentation on ranking functions if you haven't already.

    you could probably combine these two into a single column with something like:

    Rank(Sum([ViolationAmt]) OVER ([AccountID]))

    but I haven't tested this at all. again, if you put in a bit more detail about what you're trying to accomplish it will help you get a better, more detailed answer :)