5 Replies Latest reply on Feb 6, 2013 9:29 PM by Joe Mako

# Is there a formula to pick the last value in a partition?

Hi,

Is there a way to pick the last value in a partition? See my data layout below.I'd like to sum across a few categories using only the last value of the partition's time period. When time period is changed to monthly, it'd take the last day of the month's value.

Appreciate any help on this.

Thanks,

Siew

• ###### 1. Re: Is there a formula to pick the last value in a partition?

LOOKUP(SUM([Value]),LAST())

if you would like to see this in use, please provide a packaged workbook with sample data that represents your situation.

• ###### 2. Re: Is there a formula to pick the last value in a partition?

Thanks Joe for your initial help. I've attached a twbx as suggested.

In the workbook, I'm trying to build a table like below, then produce a graph based on that.

Week of
Tree# A Running Total
Tree# B Running TotalCumulative Yield So Far
Dec 30, 2012164176340
Jan 6, 2013183183366
Jan 13, 2013192196

388

Jan 20, 2013201202403
• ###### 3. Re: Is there a formula to pick the last value in a partition?

Date is used as the compute using, addressing, making Week partition, so the LAST() function returns 0 for the last date in each week.

Because you do not want to see each date's value, I used LAST() as a filter instead of an offset lookup.

To prevent Data Densification, the Date pill used for compute using is an integer instead of the date data type.

Then to connect the dots as a line, Week is a continuous, green, pill the date number pill is on the Path shelf.

This type of work is on the weird side of Tableau. There are other ways to accomplish this that may be a better fit for your situation.

1 of 1 people found this helpful
• ###### 4. Re: Is there a formula to pick the last value in a partition?

Wow. Thanks for walking me through your solution. I've learned a new trick today

The chart is missing in [result jm] sheet, with the error '... Is Last requires a field that is missing. ...' . I tried adding date number pill in rows, and I got the points, no lines though. Is that right? Attached screenshot.

My next step is to combine tree #A and B result together, e.g. tree #A's result for date number 41296 of 201 should be added with B's 41297's 202 and plot that sum, while ignoring the other rows in the partition. Can tableau do this?

A little more background, this problem stems from my need to show a running total that starts calculation from the beginning of my data set vs tableau's running total that at the most covers what's visible on the graph. Perhaps, I'm approaching this whole thing wrong

• ###### 5. Re: Is there a formula to pick the last value in a partition?

What version are you using, looks like an old v7 error, you should be on 7.0.12, and can download from:

https://licensing.tableausoftware.com/esdalt/