4 Replies Latest reply on Dec 28, 2012 10:49 AM by Dan Gerena

# Computing a MAX for a window of time, without showing the elements of time in the table

So I've got a transactional detail, and each record has a date field. If the user filters the data for a single month (say January), I want to know for the month of January for instance, what the MAX # of daily transactions was. So if Jan had 3100 transactions, the AVG per day might be 100, but I want to know what the MAX was in that window of time (let's say one day in particular was really busy, thus had 250 transactions the result shown would be "250").

If they change the filter to Q1, and if that day that in question had 250 is still the MAX for the quarter, that's what I want to show...

Any way to accomplish?

• ###### 1. Re: Computing a MAX for a window of time, without showing the elements of time in the table

Dan, first off "Happy Holidays!"

Long answer: mock up a bit of your data (nothing sensitive) and post it in an Excel file.

Good news? I'm almost certain you can do what you want.

Happy New Year!

--Shawn

• ###### 2. Re: Computing a MAX for a window of time, without showing the elements of time in the table

Happy Holidays to you too!

Attached is some sample data (columns A and B)

I then pivoted the data in Excel to get the results below, and then did an AVG of the monthly count to get the desired result in aa single cell. This single value (203.33) is what I want to display in Tableau.

 Row Labels Count of ID 1 199 2 190 3 193 4 211 5 201 6 224 7 191 8 198 9 200 10 192 11 228 12 213 Grand Total 2440 203.3333

Additionally, I pivoted it by day in Excel and sorted highest to lowest, to determine the MAX day was "15" records...I want to show that in Tableau WITHOUT having to show the individual dates...

 Row Labels Count of ID 12/12/2011 15 11/16/2011 15 9/14/2011 14
• ###### 3. Re: Computing a MAX for a window of time, without showing the elements of time in the table

See the attached. I didn't know how you wanted the date filter defined, I set it up as a continuous filter and created a parameter to let the users choose the aggregation they want (month or quarter) over that period. There's no way that I know of in Tableau for a calculated field to know the run-time settings of a relative date filter, so it seems like it's going to require some sort of parameter to know the desired window.

The window is then used to create a discrete dimension that can be used for partitioning the table calculations that are used to generate the avg or maximum. Pretty much any time you want the result of one row to affect calculations in another row, or you want to look across rows in the view, you're going to need a table calculation.

Jonathan

• ###### 4. Re: Computing a MAX for a window of time, without showing the elements of time in the table

Awesome...thanks Jonathan!