Hi,
Can Aggr() and Inter-chart functions be used together if the when the both are on a field that's not a dimension in the chart.
To illustrate I'll use my own problem:
Here is the dummy data of sales per consultant, with a 3 month rolling average.
| CONSULTANT | Sale Capture YM | Sum(Sales) | RangeAvg(Above(Sum(Sales),0,3)) |
| 90 | 1228.833333 |
| John | 201012 | 30 | 30 |
| John | 201101 | 106 | 68 |
| John | 201102 | 161 | 99 |
| John | 201103 | 121 | 129.3333333 |
| John | 201104 | 93 | 125 |
| John | 201105 | 40 | 84.66666667 |
| Jane | 201010 | 26 | 26 |
| Jane | 201011 | 65 | 45.5 |
| Jane | 201012 | 33 | 41.33333333 |
| Jane | 201101 | 150 | 82.66666667 |
| Jane | 201102 | 145 | 109.3333333 |
| Jane | 201103 | 166 | 153.6666667 |
| Jane | 201104 | 96 | 135.6666667 |
| Jane | 201105 | 34 | 98.66666667 |
Now this works fine. But if I want to calculate the average of each consultants' rolling averages in a straight table. The expression I used was:
Avg(Aggr(RangeAvg(Above(Sum(BASE),0,3)),CONSULTANT,[SALE CAPTURE YM]))
With Consultant as my only dimension.
But this obviously didn't work, and I assume it's because of the Above() function.
Is what I'm trying even possible. Please, if someone can point me in the right direction?
