9 Replies Latest reply on Jan 15, 2020 1:58 PM by Bryce Larsen

# % change first vs last?

I need to calculate the % of change of the last value vs the first for each of a specified record set, and order the results.

1. e.g. I have Name, DateTime, and Rate. For each “Name” I want to see the percent of change for the oldest vs the newest record in order largest to smallest.

I have been able to use the Table Calculation “Percent Difference From”, Relative to first, to create a table with the data, but I need to limit the results to only the last value (% difference from first).

• ###### 1. Re: % change first vs last?

Hi David,

You should be able to use something like

WINDOW_MAX(IF LAST()=0 THEN [Rate] END) - WINDOW_MAX(IF FIRST()=0 THEN [Rate] END)

I believe you'll want to compute using DateTime (or simply Table Across). Give that a shot and let us know if it works!

If not, we can try to whip up a sample or you can provide a sample twbx.

Best,

Bryce

• ###### 2. Re: % change first vs last?

Hello David.

Use this calculation to find the difference between the first value and last value in each row. I used this calculation on the attached workbook using superstore data.

(

window_max(if first() = 0 then SUM([Sales]) end)

-

window_max(if last() = 0 then SUM([Sales]) end)

)

/

window_max(if first() = 0 then SUM([Sales]) end)

Hope this helps!

Robert Breen

• ###### 3. Re: % change first vs last?

Thanks!

This looks like it might provide the value I am looking for, but I have been unable to find a way to show just the single result for each entity.

See examples below. If I don't include some sort of date function for columns I get 0 for each name. My data can cover up to 4 years and I still need a single result.

• ###### 4. Re: % change first vs last?

Hi David,

You can use the same formula, but try moving your date field onto the Detail pane. Then you can also create a calculated field titled LAST()=0 (using same Compute Using previously mentioned) and drag that onto the filter pane.

Example using Superstore attached.

Best,

Bryce

• ###### 5. Re: % change first vs last?

Thanks but I am not clear on what the "Detail pane" is. Tried putting the date in a few places but none of them seemed to work.

Second, your reply, and Roberts, says they have examples attached, but I don't see any attachments?!?!

• ###### 6. Re: % change first vs last?

Hello David,

If you go to this page, you will see the attachment on my post. Let me know if the attachment helps.

• ###### 7. Re: % change first vs last?

Crud, looks like I am using an old version of Tableau (10.4). I will need to contact our IS department and see about getting it upgraded (that is likely to take days if not weeks).

• ###### 8. Re: % change first vs last?

No problem. Here's a 10.3 version.

• ###### 9. Re: % change first vs last?

Hi David,

Just wanted to reattach the file in 10.3 as well just in case.

Also, the Detail is under the Marks card:

Anything added here is in the background of your viz (essentially partitions the data). This allows you to use these items in table calculations and the like without it being visible in your actual visualization.

In order to see attachments, you'll have to head to the actual forum post rather than just opening your messages/inbox - just a heads up!

Best,

Bryce