7 Replies Latest reply on Aug 25, 2015 1:00 PM by drew k

# Table calculation across a category

I have a calculated field that displays the difference between the current date and the previous for a list of dates. However, I have multiple categories that each contain their own dates and I want to be able to compute average differences for each separate category. So, I want this difference counter to start over when it comes to a new category. I've circled the places where the counter should reset to 0.

I've also attached the workbook containing this worksheet. Any ideas on how I can achieve my desired result? Or perhaps if there is a better tool suited for this problem?

Thanks

• ###### 1. Re: Table calculation across a category

Another problem I've run into is: in calculating the Average Differences, I want to be able to remove outliers in the calculation. However, the way I've created my calculated fields makes this difficult to achieve. I made my 'Difference in Dates' field according to this knowledge base article: http://kb.tableau.com/articles/knowledgebase/difference-dates-one-date-field. This is the calculation:

DATEDIFF('day', MAX([Reference Date]), MAX([Order Date]))

When I add it to Marks and make it a Difference quick table calc, it becomes this:

ZN([Difference in Dates]) - LOOKUP(ZN([Difference in Dates]), -1)

To create a calculated field that displays the average of these, I made 'Average Differences' which is

WINDOW_AVG(ZN([Difference in Dates]) - LOOKUP(ZN([Difference in Dates]), -1))

The second sheet in the workbook illustrates my problem

• ###### 2. Re: Table calculation across a category

here's a little trick.  You can format the number to be blank if it's negative by going with a custom format.

1 of 1 people found this helpful
• ###### 3. Re: Table calculation across a category

What am I doing wrong?

• ###### 4. Re: Table calculation across a category

oh yeah.  add a space after the semi-colon

• ###### 5. Re: Table calculation across a category

Nevermind. I was able to achieve the desired result by using "#,##; ;"

My only problem now is that these numbers are still being used in my Average Difference calculation. I think I need to figure out a different way to calculate it.

• ###### 6. Re: Table calculation across a category

can you use a median to remove the outliers? instead of avg?

• ###### 7. Re: Table calculation across a category

Good question. The formula I was given requires an average of these differences, but I supposed a median would suffice for larger data sets. However, I am not sure if it would work for drilling down to smaller data sets with say ~30 records.