Spotfire: how to get the First and last value in a column based on entity and date?

Question

I have a simple table with two entities and values associated with dates. I want to extract the FIRST and LAST value based on historical dates. In the underlying data table, the dates are not sorted, hence when using FIRST() and LAST(), Spotfire gives incorrect values. What is the best way to solve this?

I tried

First([Value) OVER (Intersect([Category],[Date]))

Sample of the dataset:

Answer 1

If your using a cross table you can use a nested If statement to return the values when date is Min and Max.