How to create one table

[#1]

Hi All,

I have the following 2 tables:

Actual Table:

YearMonth, Category ,ActualRevenue

2013-1-1, A, 200

2013-2-1,A,300

2013-3-1,A,500

Focast table:

YearMonth, Category, FocastRevene

2013-4-1,A,0

Based onthe above 2 tables, I want to focast the revenue for the second table, the logic is Take the average of the last 3 months and the Category is the same.

So for YearMonth=2013-4-1, the Focast revenue (200+300+500)/3

How to use scrip to do it?

Thanks.

[michael_maeuser]

load

MonthsStart(1, Today(), 0) as YearMonth

avg(if(month(yearmonth)>= month(today())-3, actualrevenue) as ActualRevenue

resident ActualTable ;

[michael_maeuser]

sum instead of avg of course and in the if expression add year(yearmonth)=year(today())