CODEWITHSUNDEEP
spotfire
Connor Walsh
Connor Walsh

Reputation: 25

Ranking Dates Based on Another Column - Spotfire

Does anyone know of way to circumvent the Spotfire limitation for using the OVER function to RANK or order dates when using a custom expression?

Providing a little background, I am trying to identify or mark a lease based on the below data as 1, 2, 3 etc. For example, since we see twice 63 in the left column, I would like to return a 1 and a 2 to identify the two different leases, starting on 1/1/2016 and 8/1/2016. Then a 1 and 2 for 72, a 1 for 140 and so one. Unfortunately, OVER functions can only be used with aggregation methods and I don't know of another method to produce the result that I am looking for.

Tenant Lease_From  Lease_To     Tenant_status
63     1/1/2016    1/31/2017    Current
63     8/1/2017    7/31/2018    Current
72     10/1/2016   7/31/2017    Current
72     8/1/2017    7/31/2018    Current
140    2/1/2017    7/31/2018    Current
149    8/1/2016    7/31/2017    Current
149    8/1/2017    7/31/2018    Current
156    1/15/2017   3/31/2018    Current
156    4/1/2018    3/31/2019    Current

Upvotes: 2

Views: 1717

Answers (2)

niko
niko

Reputation: 3974

please consider @blakeoft's answer as the correct one!

that said, as an FYI, First() is considered an aggregation method, and OVER statements can be included inside of an If()! so you can accomplish the same thing with an expression like:

If([Lease_From] = First([Lease_From]) OVER ([Tenant]), 1, 2)

when you combine If() and OVER in this way, you can get some really cool and powerful visualizations, BUT you do lose the ability to mark data effectively. this is because the expression is evaluated from the context of the If() rather than the OVER; in other words, all rows are considered instead of only the ones selected.

you can get around this with some black magic (AKA data functions) but it's a bit contrived.

again, in this situation, Rank() is absolutely the correct solution.

Upvotes: 1

blakeoft
blakeoft

Reputation: 2400

Use this:

Rank([Lease_From], [Tenant])

Gives this as the result:

Tenant Lease_From  Lease_To     Tenant_status Rank([Lease_From], [Tenant])
63     1/1/2016    1/31/2017    Current       1
63     8/1/2017    7/31/2018    Current       2
72     10/1/2016   7/31/2017    Current       1
72     8/1/2017    7/31/2018    Current       2
140    2/1/2017    7/31/2018    Current       1
149    8/1/2016    7/31/2017    Current       1
149    8/1/2017    7/31/2018    Current       2
156    1/15/2017   3/31/2018    Current       1
156    4/1/2018    3/31/2019    Current       2

Upvotes: 3

Related Questions