7 Replies Latest reply on Jan 8, 2014 12:51 PM by Matt Lutton

# Getting latest state in a time series

I know this should be simple but I'm stuck trying to aggregate that latest state value per user in a set of user time series data. Here are the individual rows:

 user date state lisa 1/1/2013 unknown lisa 1/2/2013 signup lisa 1/9/2013 signup lisa 1/10/2013 activated lisa 1/11/2013 activated lisa 1/12/2013 active lisa 1/13/2013 idle bart 1/1/2013 unknown bart 1/2/2013 signup bart 1/9/2013 signup bart 1/10/2013 signup bart 1/11/2013 activated bart 1/12/2013 activated bart 1/13/2013 activated

I want to be able to rollup to just the user's names and see the lastest state:

 user state lisa idle bart activated

Example worksheet attached with this data.

• ###### 1. Re: Getting latest state in a time series

Hi Omar,

1.- Create a Table Calculation, and name it as Latest state in time series with the formula:

```IF INDEX()==1 THEN LOOKUP(ATTR([state]),LAST()) END
```

2.- Drag user to Rows shelf

3.- Drag date to Detail in Marks shelf, and change YEAR(date) to DAY(date)

4.- Drag the table calculation Latest state in time series to Text in Marks shelf and define Compute using date

Take a look at worksheet Sheet 2 in the attached workbook

I hope this helps

Best

Ramon

• ###### 2. Re: Re: Getting latest state in a time series

Here's another approach using just the LAST() function, with proper compute using settings:

Calculated field is simply: LAST()

Then:

1) Place this LAST() field on the filters shelf

2) Set compute using for dimensions in the view:

3) Keep only 0 values (the last row for each user--this assumes your dates are in order, which they are)

And the final view:

8.1 workbook attached.  Many thanks to Joshua Milligan and Jonathan Drummey for providing enough examples and descriptions for this to finally make sense to me!

1 of 1 people found this helpful
• ###### 3. Re: Re: Getting latest state in a time series

Thanks both for the solutions. Of course my real data and presentation is more complex as I'm presenting sums of measures in the text marks and so need access to all of the rows. Matthew's solution didn't fit well with this scenario and Ramon's worked better but didn't include all the rows needed for the totals.

What I was attempting to do is to display the rolled up totals colored by the last state and I think these two objectives are in conflict. I'm going to include the last state in the custom connection SQL in order to get around this.

Again much thanks for the responses.

• ###### 4. Re: Re: Getting latest state in a time series

Table Calculation filters, like LAST(), are evaluated after the data is brought into Tableau and after most other filters, so you should still have access to all the rows of data.  I'm not quite sure what you mean.

• ###### 5. Re: Re: Getting latest state in a time series

I found that I had to filter my Last State measure to remove Nulls, otherwise i ended up with multiple sum'd measures in the text mark. I'll see if I can work up a richer example than the initial one I provided.

• ###### 6. Re: Re: Getting latest state in a time series

OK, I simply added a "data" column to the input data and placed SUM(data) in the text mark. The totals seem to reflect a single row rather than the sum of all the rows. Built this based upon the solution example provided by Matthew.

• ###### 7. Re: Re: Getting latest state in a time series

Very easy fix.  Simply make the calc: WINDOW_SUM(SUM(data)), with the same compute using settings as before:

Final result attached.  This would not work with some other methods, again, because Table Calculation filters are evaluated AFTER most everything else.  Ramon's solution uses table calcs, as well, so I'm not sure what that would look like in your production workbook.

For more information on Tableau's "order of operations", check this out (I have this printed and pasted on my cubicle wall!): http://community.tableau.com/message/139603#139603