You need to take care that Period is sorted chronological in load order (like in your sample data) to make this solution using advanced aggregation work with your real data.
comm175632.qvw 162.0 K
Thanks for your response. It works in the sample data, but not in my real data.
I've sorted Period and Region in the load script:
Resident Activity_1 Order by PERIOD, REGION;
DROP Table Activity_1;
But when I apply the aggr formula the data breaks again:
Aggr(RangeSum(Above(Sum(ACTIVITY * PERCENTAGE/100),0,MONTH)),REGION,PERIOD)
Unfortunately, you won't change LOAD order of a field if you do something like
ORDER BY Field;
DROP TABLE T1;
When you LOAD Field the second time, the load order of the field will still be determined by the first load (It's not about the order of values in the table, but in the field).
You need to do something like this:
Create a temporary table with Period values ordered chronological first in your script, something like
MonthName(AddMonths(Makedate(2011), recno()-1) ) as Period
Then LOAD your data:
Month(Period) as Month,
Year(Period) as Year,
(biff, embedded labels, table is Sheet1$);
Then drop the temp table:
DROP TABLE Tmp;