To reach your requirement follow the steps:
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
FindLastStateInSeries_RM.twbx 31.8 KB
1 of 1 people found this helpful
Here's another approach using just the LAST() function, with proper compute using settings:
Calculated field is simply: LAST()
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:
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.
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.
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.
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.
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