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

    Combine Rows Before Calculation

    Michael Reilly

      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?

       

      Thanks in advance!