topic Forecasting Table in QlikView

Forecasting Table

Mon, 13 Oct 2014 18:12:27 GMT

Maybe i'm making this too complicated...

I have a simple sales dashboard...

I want to create a straight table chart that displays...

Month\|Year (FUTURE)	Revenue
Next month year (11/1/2014)	3 year average for November...
12/1/2014	3 year average for December
1/1/2015	3 year average for January...

I have a "MasterDate_MonthYear" field that is built for this....just struggling on how to set this up...

Re: Forecasting Table

Gysbert_Wassenaar — Tue, 14 Oct 2014 09:11:34 GMT

You will likely have to add a small table in the script that links each month to the months of the 3 years you want to use for the average. Perhaps something like:

ReportMonthYear, [Month|Year]

11/1/2014, 11/1/2011

11/1/2014, 11/1/2012

11/1/2014, 11/1/2013

12/1/2014, 12/1/2011

12/1/2014, 12/1/2012

12/1/2014, 12/1/2013

...etc

Then use ReportMonthYear as dimension in your straight table.

Re: Forecasting Table

jagan — Tue, 14 Oct 2014 09:28:17 GMT

Hi David,

Try like this

Dimension: None

Expressions:

Next Month Year: Caption Expression = MonthStart(Today(), 1)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -11))<=$(=MonthEnd(Today(), -11))'} +

{'>=$(=MonthStart(Today(), -23))<=$(=MonthEnd(Today(), -23))'} +

{'>=$(=MonthStart(Today(), -34))<=$(=MonthEnd(Today(), -34))'} >} MeasureName)

December:Caption Expression = MonthStart(Today(), 2)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -10))<=$(=MonthEnd(Today(), -10))'} +

{'>=$(=MonthStart(Today(), -22))<=$(=MonthEnd(Today(), -22))'} +

{'>=$(=MonthStart(Today(), -33))<=$(=MonthEnd(Today(), -33))'} >} MeasureName)

January:Caption Expression = MonthStart(Today(), 3)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -9))<=$(=MonthEnd(Today(), -9))'} +

{'>=$(=MonthStart(Today(), -21))<=$(=MonthEnd(Today(), -21))'} +

{'>=$(=MonthStart(Today(), -32))<=$(=MonthEnd(Today(), -32))'} >} MeasureName)

Regards,

Jagan.

Re: Forecasting Table

Tue, 14 Oct 2014 17:33:57 GMT

I was also able to to use an aggr function on the Month of the future Month/Year

Example:

aggr(sum(Sales),month) using the month of the future month year