8 Replies Latest reply on Jan 10, 2014 11:06 AM by Matt Lutton

# How to aggreagate individual date duration average duration?

Attached workbook has an average duration for individual IDNUMBER calculated based on max([TREATMENTDATE])-min([LOSSDATE])

This works fine, however I would like to create and average TXDuration for all IDNUMBERS aggregated for each CPTCODE in Column1.  I am fairly new to Tableau and i would greatly appreciate your help.

Same question was posted here: http://community.tableau.com/message/248630#248630 by Brett, but never answered.

Thank you,

• ###### 1. Re: How to aggreagate individual date duration average duration?

What is your expected output/result?  I'm not sure I understand what you're looking for.  Tableau works at the level of detail you introduce, based on the dimensions in your view.  For example, in the TX Duration for each IDNUMBER sheet, there is no YEAR(LOSSDATE) pill on the columns shelf, but the Needed Output sheet does include YEAR(LOSSDATE).  If we add YEAR(LOSSDATE) to the TX Duration sheet, and turn on all subtotals from the analysis menu, we'll see that the totals match the aggregates in your Needed Output sheet.

So, I may not understand the problem--can you explain in more detail, and provide the output you are expecting/mock up of the expected results?

• ###### 2. Re: How to aggreagate individual date duration average duration?

Hi Matthew,

This is the output that I would like to get:

 Average TX Duration Year of LOSSDATE Year of LOSSDATE CPTCODE 2012 2013 97012 55.97 47.98 97124 54.53 46.99 97140 64.83 54.46

So basically, I need an average tx duration for all IDNUMBERs per year per CPTCODE. The subtotal only looks at the difference between MAX(TREATMENTDATE) - MIN(CLAIMLOSSDATE) considering all IDNUMBER.

Sorry if I made it even more confusing. Please let me know if you have any other questions.

• ###### 3. Re: Re: How to aggreagate individual date duration average duration?

Here you go--I created another calculated field of:

IF FIRST()==0 THEN WINDOW_AVG(max([TREATMENTDATE])-min([LOSSDATE])) END

A table calculation is needed to compute this because of the dimensions you want to compute across.  Note ID Number is on the details shelf, because it is needed in the view to calculate this properly.

The IF FIRST()==0 is an optimization technique.  Without it, we'd return more than one value in each cell, with overlapping text.

The Table Calculation's advanced settings are (click to enlarge)

Finally, we use the Analysis>>Stack Marks>>OFF option to display the results in a clean format:

The attached workbook is in version 8.1, as you didn't mention what version you were on in your original post.  I'd recommend doing that in the future.  I can re-create this in version 8 if needed.

Cheers. I sometimes don't explain things as well as some of the more experienced forum members, so feel free to ask questions.

• ###### 4. Re: How to aggreagate individual date duration average duration?

Thank you, it looks very promising, but i was unable to open it since I am still using 8.0. If it is not a big inconvenience, could you please recreate it in 8.0?

• ###### 5. Re: Re: How to aggreagate individual date duration average duration?

If you anticipate needing to do this type of thing often, here is some suggested reading:

Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

I'll re-create in 8.0, but the steps/process are exactly the same.

• ###### 6. Re: How to aggreagate individual date duration average duration?

Here is the 8.0 version (attached)

• ###### 7. Re: How to aggreagate individual date duration average duration?

It worked perfectly thank you very much for your help. I will definitely check the training section. Have a good weekend.

• ###### 8. Re: How to aggreagate individual date duration average duration?

No problem--make sure to mark the question as "answered" in some way so others know you've resolved this.  Cheers.