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?
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.
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.
Sample Data Table Calc ML.twbx 155.7 KB
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?
If you anticipate needing to do this type of thing often, here is some suggested reading:
I'll re-create in 8.0, but the steps/process are exactly the same.
It worked perfectly thank you very much for your help. I will definitely check the training section. Have a good weekend.
No problem--make sure to mark the question as "answered" in some way so others know you've resolved this. Cheers.