7 Replies Latest reply on Mar 28, 2019 4:17 AM by Robert Breen

# Continuously adding previous subtracted dates

I'm trying to make a formula that add each previous subtracted dates, I want the formula to calculate the difference from the first and second dates then then add the result to the difference between the second date and the third date and so on.

I found this link that calculates the difference between a date and the previous date: https://kb.tableau.com/articles/howto/calculate-difference-in-dates-with-one-date-field

but in the link it doesn't add the previous subtracted dates, so how can I do that?

thanks

• ###### 1. Re: Continuously adding previous subtracted dates

Hello Owdrc,

Table calculations can be used for this.

1. Find the difference between each date.

DATEDIFF('day', LOOKUP(max([Order Date]), -1), max([Order Date]))

2. Calculate the running total of the previous calculation

RUNNING_SUM([Days since last Date])

Hope this helps! See Attached.

Robert Breen

• ###### 2. Re: Continuously adding previous subtracted dates

Thanks for the response, I've done it exactly like what you explained and it worked fine but the problem is that when I do the steps of the table calculation as what the link above did it shows null values in both "days since last date" and "running total" columns, I'm not allowed to share the data but below is sample of the part that I worked on:

Id               date

1               April 8, 2014

2               November 18, 2013

3               February 25, 2014

4               May 6, 2014

5               January 18, 2015

6               January 18, 2015

7               February 1, 2015

8               February 1, 2015

9               January 25, 2015

10               February 8, 2015

Thanks,

• ###### 3. Re: Continuously adding previous subtracted dates

Hello Owdrc,

I replicated the data you sent above. Make sure date is formatted as a date and the table calculation is calculating on table down. See attached.

• ###### 4. Re: Continuously adding previous subtracted dates

thanks for the answer it works. But what if I want the average of the “running total” by the id? I tried to use the following calculation:

running total / count(id)

but it shows null. I want to only show the average of the running total by id in the sheet (only a number) without the date and id columns.

other question is that is there any other possible way to continuously add each previous subtracted dates? Because I have a huge database with many dashboards and this calculation make the performance too slow and some times the file stop working “not responding“

Many thanks

• ###### 5. Re: Continuously adding previous subtracted dates

Hello Owdrc,

You can use this calculation to output the average. Also calculated on table down.

WINDOW_AVG([running total])

Unfortunately, table calculations run a little slow.

• ###### 6. Re: Continuously adding previous subtracted dates

This works but only if I use the id column in the Rows, if I remove the id from the Rows it will show a null. what I want is just a sheet contain only the average without dragging any dimension to Rows or Columns

• ###### 7. Re: Continuously adding previous subtracted dates

Hello Owdrc,

Since we are using table calculations, you will need to have the table available for the calculation.

You can use last() = 0 to hide every row except the last one. Then hide each of the headers, and you will only see the average.

See attached.

Robert