0 Replies Latest reply on Aug 22, 2013 11:54 AM by Matt Stoffa

    aggregate function based on aggregation "level"

    Matt Stoffa

      How can I create an aggregate function that is aware of its own aggregation “level”?  I know that doesn't make sense, so let me explain.  Let’s say my data source looks like the rows below (2 days, 2 hours, 4 quarter hours per hour):

       

       

      Date

      Hour

      Qtr Hour

      Total GCs

      July 1

      11:00

      11:00

      20

      July 1

      11:00

      11:15

      35

      July 1

      11:00

      11:30

      34

      July 1

      11:00

      11:45

      46

      July 1

      12:00

      12:00

      49

      July 1

      12:00

      12:15

      28

      July 1

      12:00

      12:30

      39

      July 1

      12:00

      12:45

      48

      July 2

      11:00

      11:00

      27

      July 2

      11:00

      11:15

      30

      July 2

      11:00

      11:30

      27

      July 2

      11:00

      11:45

      46

      July 2

      12:00

      12:00

      45

      July 2

      12:00

      12:15

      53

      July 2

      12:00

      12:30

      47

      July 2

      12:00

      12:45

      42

       

       

      I want to create a variable that can tell me the Average Total GCs (regardless of how the data is aggregated).

       

       

      If the report is at the quarter hour level, the very simple Avg(Total GCs) will work fine because it’s the average value of the quarter hour data …

       

       

      Hour

      Qtr Hour

      Avg Total GCs

      11:00

      11:00

      23.5

      11:00

      11:15

      32.5

      11:00

      11:30

      30.5

      11:00

      11:45

      46

      12:00

      12:00

      47

      12:00

      12:15

      40.5

      12:00

      12:30

      43

      12:00

      12:45

      45

       

       

      What if we want to roll this up by HOUR?  This won’t work because it will still calculate the average of the QUARTER HOUR data rows:

       

       

      Hour

      Avg Total GCs

      11:00

      33.125

      12:00

      43.875

       

       

      The results should sum to the hourly level first and then take the average of those results.  So it SHOULD be …

       

       

      Hour

      Avg Total GCs

      11:00

      132.5

      12:00

      175.5

       

       

      And the whole day (assuming the entire data set only includes the original 16 rows) would be …

       

       

      Date

      Avg Total GCs

      July 1

      37.375

      July 2

      39.625

       

       

      And these daily results should be …

       

       

      Date

      Avg Total GCs

      July 1

      299

      July 2

      317

       

       

      What if we want to find the Average DAILY Total GCs?  Again, let's assume our data set is ONLY the 16 original rows.

       

      Avg Total GCs

      38.5

       

      It should summarize the Total GCs per day first and then average them ...

       

      Avg Total GCs

      308

       

      Is there any way to do something like this?  I think the key is for the aggregate function to be "aware" of its own aggregation level.  For example, if it knows that it's grouping by HOUR, the calculation is X.  If it's grouping by DAY, the calculation is Y.

       

      Thanks.