2 Replies Latest reply on Sep 8, 2016 10:49 AM by Alexander Mou

# First/last value in a time series

Hi,

so this is a question that has multiple solutions online but I haven't been able to get any of them to work for a time series. The problem is very simple: I have time series data (y,t) and I want to create calculated fields for the first and last y values in the time series.

I've tried using INDEX(), LAST(), FIRST(), LOOKUP(), etc. but haven't been able to figure out how to get them to work properly.

A sample workbook and two data files can be found in the attached .zip file.

-Simo

• ###### 1. Re: First/last value in a time series

hi Simo,

So to use a Table Calculation, such as LOOKUP, the measure needs to be an aggregate. As you have your measure [extension (mm)] dis-aggregated, we can wrap it in a MIN (or MAX or AVG for that matter). So the calculated fields become;

[First Value]

LOOKUP(MIN([extension (mm)]),FIRST())

[Last Value]

LOOKUP(MIN([extension (mm)]),LAST())

I've added these to the ToolTips so you can see how it's working.

1 of 1 people found this helpful
• ###### 2. Re: First/last value in a time series

Ah, Simon,

Thanks for the formula with lookup()! I didn't know we can use them like

that.

I used to use something more verbose like:

Window_Max(if first()=0 then Max(extension (mm)) end)

Window_Max(if last()=0 then Max(extension (mm)) end)

On Thu, Sep 8, 2016 at 2:46 AM, Simon Runc <tableaucommunity@tableau.com>