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

# aggregate function based on aggregation "level"

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.