11 Replies Latest reply on Aug 6, 2016 3:06 AM by Disha Shah

# How do I calculate the average DateDiff across two data sources?

I need to calculate the average DateDiff where the two dates are in separate data sources. I've attached an example similar to what I'm trying to achieve.  In the attached example, I created 'Days from Process A to Process B' to calculate the DateDiff but am unable to calculate an average due to the inability to further Aggregate. Ideally the DateDiff (in days) would be calculated for each ID and an average of 'Days from Process A to Process B' would be displayed by Location.

Any advice would be much appreciated! Thanks!

• ###### 1. Re: How do I calculate the average DateDiff across two data sources?

Very brief explanation I'm afraid.

You need to use a table calculation to compute the average of the datediff values you have calculated - because blending two data sources has already required you to use an aggregate and you can't have another aggregate in your calculation, as you noted.

Basically you need to return the date difference for each ID to Tableau, then use the WINDOW_AVG() table calculation function to work out the averages by location. But as you have one row for each ID and you really only want one row for each location, you need to filter the results so it only shows one result per location.

I've done that in the attached by filtering to the first row for each location.

If you aren't familiar with table calculations you'll need to read up on addressing and partitioning - which is what determines which rows are grouped together for the calculations.

One other thing I had to change in your example was the definition of the relationship between the two connections - you weren't joining on ID but you were joining on the process dates - so only the IDs that had a duration of zero days actually joined.

• ###### 2. Re: How do I calculate the average DateDiff across two data sources?

Hi Richard - Thank you so much for your response! I applied this solution to my actual data set and it works perfectly. I definitely need to brush up on table calculations. Thanks!!

Jaimie

• ###### 3. Re: How do I calculate the average DateDiff across two data sources?

I have one more question related to this - is it possible to add a Count of records for each Location in a column next to the Average? Thank you!

• ###### 4. Re: Re: How do I calculate the average DateDiff across two data sources?

Sure. You just need another table calculation:

WINDOW_SUM(ATTR([Number of Records])

Then use Measure Names and Measure Values to put the measures you want in the table.

1 of 1 people found this helpful
• ###### 5. Re: How do I calculate the average DateDiff across two data sources?

This is great, thank you!!

• ###### 7. Re: How do I calculate the average DateDiff across two data sources?

This is helpful and gets me on the right track.  However, I am having trouble replicating what was done in sheet 2.

On sheet 4 of the attached, I tried to replicate what Richard  has done on sheet 2.  When I look at the measures, dimensions and filters, I have done exactly the same thing as Richard has done in sheet 2.  However, on sheet 4 the filter for "First" only has an option for True and the records are not being grouped and averaged correctly.

What is the difference between sheet 4 and sheet 2?

• ###### 8. Re: How do I calculate the average DateDiff across two data sources?

The difference is the "Compute Using" setting for the table calculations.

Whenever you see a little triangle on the end of a pill on one of the shelves, like this:

or this:

that indicates a table calculation, which basically means that Tableau is calculating a value by looking at multiple rows that have been returned from the server. To do that it needs to know how to group the rows to do the calculation and what order to process them in. That's what the "Compute using" setting defines.

On sheet 4 you need to adjust the Compute using to use the ID field.

Table calculations take a bit of getting used to. I'd suggest having a read of the documentation and watching the online training videos.

2 of 2 people found this helpful
• ###### 9. Re: How do I calculate the average DateDiff across two data sources?

Wow, so great to see you posting here Richard!

Are you playing with the Tableau 10 beta at all? It's an exciting time! Hope you'll be in Austin this year for conference!

• ###### 10. Re: How do I calculate the average DateDiff across two data sources?

Richard-  that makes a ton of sense.  THANK YOU!

Was struggling with this for some time.  Really appreciate your response.

• ###### 11. Re: How do I calculate the average DateDiff across two data sources?

Hi Matt, how are you doing?

Yes, I'm a very infrequent visitor to these parts these days - too busy with other stuff. But when I get pinged because someone has posted on a thread I've been on I do still try to find the time for a quick response - mainly on the "Grow Your Own Filled Maps" thread.

I've had a bit of a look at the 10.0 beta, plus I'm on the alpha for spatial support and both are looking great.

I won't make it to the conference this year, I'm afraid - too busy, but I'm still using Tableau extensively and I definitely still regard myself as part of the family!