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.
