Easy Method of Calculating Multiple Totals through Portal

[davidrichards]

I am working on a report to show sales figures for this week vs. last week vs. 2 weeks ago vs. 3 weeks ago, etc.

Week: This week last week 2 week ago 3 week ago ...

website 1 $A $B $C $D

dept 1 $E $F $G $H ...

dept 2

dept 3

website 2

dept 1

dept 2

dept 3

...

LaRetta and kjoe taught me how to standardize the weeks into buckets. Now in order to fill out the rest of the report I have to create a separate relationship for each statistic. That is, to summarize sales for $A, I have a relationship : TargetWeek&website1 to the sales transaction data and a variable in this file: thisWeek&website1=sum(TargetWeek&website1:NetSales ) Likewise, for $B I have another variable LastWeek&website1=sum(TargetWeek&website1:NetSales ).

To compute the dept. 1 sales I have another relationship- TargetWeek&Dept1 and a variable in this file $E= ThisWeek&Dept1 = sum(TargetWeek&Dept1: NetSales).

So here's the question:

There must be a better way to accomplish this goal. This solution requires lots of relationships and lots of predefined variables. The solution falls apart once a department is changed. Not a great solution.

Any suggestions?

Thanks

Mac, OS 9 and OS X, FM 6