topic Re: How to create one table in QlikView

How to create one table

Mon, 02 Sep 2013 12:14:44 GMT

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.

Re: How to create one table

michael_maeuser — Mon, 02 Sep 2013 12:20:29 GMT

load

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

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

resident ActualTable ;

Re: How to create one table

michael_maeuser — Mon, 02 Sep 2013 12:28:18 GMT

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