11 Replies Latest reply on Aug 22, 2013 9:47 AM by Michael Reilly

# Combine Rows Before Calculation

I have a table of data with a datetime column, a "seconds" column, and various other columns.  I need to be able to drill down to each row of data in the table, but the top-level view requires that I aggregate the "seconds" of the rows that share the same datetime, and then run those aggregates through a calculated field to obtain a single value.  For example (problem statement below tables):

Data that I need Tableau to be able to drill down to:

DTTMSeconds

7/13/2013 12:00:00 AM

850
7/13/2013 12:00:00 AM1000
7/13/2013 01:00:00 AM800
7/13/2013 01:00:00 AM750
7/13/2013 01:00:00 AM900
7/13/2013 02:00:00 AM1000
7/13/2013 02:00:00 AM1200
7/13/2013 03:00:00 AM500
7/13/2013 03:00:00 AM675
7/13/2013 03:00:00 AM300
7/13/2013 03:00:00 AM450
7/13/2013 04:00:00 AM875

I need to aggregate these so as to see only the MAX(Seconds) for each DTTM, like so:

DTTMMAX(Seconds)
7/13/2013 12:00:00 AM1000
7/13/2013 01:00:00 AM900
7/13/2013 02:00:00 AM1200
7/13/2013 03:00:00 AM675
7/13/2013 04:00:00 AM875

Then I need to take these aggregated numbers and use them to calculate the % of DTTMs for which MAX(Seconds) > 900.

Here's the problem:

While I know how to display MAX(Seconds) for each DTTM in a view (as in the second table above) and I know how to write the calculated field based on the data in the data source (as in the first table above), I do not know how to write the calculated field such that it acts upon the MAX(Seconds) for each DTTM.  Can someone please point me in the right direction?

• ###### 1. Re: Combine Rows Before Calculation

Hi Michael,

So you're looking for the percent of DTTM that have Max(Seconds)>=900? I.E. 3 out of 5?

First, create a calculated field similar to the following:

if max(Seconds)>=900 then (countd([DTTM])) end

Then, create a second calculation:

window_sum([Calculation1])/total(countd([DTTM]))

Right click on Calculation2 and select Default Properties>Number Format...>Percentage.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Combine Rows Before Calculation

Hi Tracy,

Thanks for your response.  It is a step in the right direction, but I neglected to mention that I need the calculation to vary depending on the chosen timeframe for the visualization.  That is, the user should be able to slice the data by hour, day, week, month, year, etc., and the percentage(s) should update accordingly.

Thus, if they are looking at the above data for the entire day of 7/13/13, they should see a single value: 60%.

However, if they choose instead to look at the data by hour, they should see a different value for each hour (in the example: 100%, 100%, 100%, 0%, 0%).

In reality, there are thousands of data points spanning several years.  A common use case will be for the user to start with a single value for 2013, then drill down to one value per month, then week, then day, until they are finally looking at the individual rows in my original table.  I would like for the user to be able to drill down by expanding (or contracting) the date field with Tableau's "+" button.

Is there a way to generalize your above solution to achieve this?

Thanks again,

- Michael

• ###### 3. Re: Combine Rows Before Calculation

Hi Michael,

Please post some sample data in a packaged workbook, then someone here can help. See http://community.tableau.com/docs/DOC-5065 for details.

Jonathan

• ###### 4. Re: Re: Combine Rows Before Calculation

Thanks Jonathan,

I've attached a packaged workbook.  It has two data sources: Sheet1 and Sheet2.  Sheet1 is a subset of the real data I need to use to create my visualization.  Sheet2 is a manipulation of the Sheet1 data that I use to show what the visualization should look like.  I need to be able to create the visualization directly from the Sheet1 data source (because it is infeasible to create an equivalent of Sheet2 for the real data set).

Any help will be greatly appreciated!

• ###### 5. Re: Re: Re: Combine Rows Before Calculation

See the attached. I didn't try to completely follow your logic on what's a pass vs. fail, I set up the basics here. It uses a table calculation to aggregate over the MAX(Seconds) to calculate the % of DTTMs, I set the compute using to the DTTM but it could just as well be Table (Down) in this view. This will work as you click on the + sign to drill down the date hierarchy.

Jonathan

• ###### 6. Re: Re: Combine Rows Before Calculation

Hi Jonathan,

Thanks for the attempt to solve my problem.  I took a look at it, but unfortunately, it still counts the MAX(Seconds) across all data points and uses that as the numerator in calculating the % of DTTMs.  What I need is to compare the MAX(Seconds) across each distinct DTTM, then sum the number of those that are below (or above) 900 and use THAT as the numerator (the denominator being the number of distinct DTTMs rather than the number of rows).

So, in the example you sent, the value for "% of DTTMs" should be 60% rather than 100%.

• ###### 7. Re: Re: Combine Rows Before Calculation

I think I'm starting to understand this now. What defines a DTTM, exactly? Is it the time down to the hour, or something else?

• ###### 8. Re: Re: Combine Rows Before Calculation

Currently, a DTTM is the time down to the hour, but it is feasible that it would be down to the minute in the future.

(DTTM is my shorthand for "datetime," btw)

• ###### 9. Re: Re: Re: Combine Rows Before Calculation

See the attached. What I did was duplicated the DTTM dimension so that there's a "DTTM for drill" that is used to drill up and down the hierarchy. The % of DTTM calc is modified to only return one value per partition, and still has a compute using on DTTM. That way it will partition on the DTTM for drill dimension. I also modified the DTTM Flag calc so it computes according to your original spec, and put a copy of the % of DTTM calc on the Filters Shelf, filtering for non-Null values. That way it won't end up with mark stacking.

Jonathan

• ###### 10. Re: Re: Re: Combine Rows Before Calculation

Jonathan,

Thank you so much - this is a very clever solution!  I am integrating your solution into my worksheet and will report back to confirm that it worked or ask another question .

Best,

- Michael

• ###### 11. Re: Combine Rows Before Calculation

Thanks Jonathan!  I was able to adapt your solution into what I needed.