topic Forecasting with selections in QlikView

Forecasting with selections

Wed, 14 Sep 2011 06:30:56 GMT

Hi,

I have a table with field:

Date,

Enrollment Count,

State,

Tehsil

and some more fields...

A state has one or more tehsil in it.

So a sample data would look like

Date Enrollment Count State Tehsil

01/09/2011 12 X a

?01/09/2011 13 X b

01/09/2011 11 Y c

01/09/2011 19 Y d

02/09/2011 15 X a

02/09/2011 17 X b

02/09/2011 13 Y c

02/09/2011 14 Y d.

This goes on....

Wish 1:

I would like to plot a chart with x axis as date, y axis as sum of enrollment count. For dates which the data exist, it should give the actual values. For future dates, it should forecast the values. The forecast can be a regression line, or an average of the last fifteen days.

Wish 2:

I have a list box, which would enlist the state. If I select a particular state(say X), then the above chart should reflect the actual and forecast data for that particular state.

Similarly, I have a list box which enlists the tehsil. I should be able to select a particular tehsil and get its corresponding actual and forecast data.

Any help/suggestions would be appreciated.

Regards,

Rajiv

Forecasting with selections

Jason_Michaelides — Wed, 14 Sep 2011 06:38:41 GMT

Would a trend line be OK for the forecasting or do you need something rather more sophisticated? I've not used them much but I believe there are several to choose from at the bottom of the chart Expression tab. If so, then just a standard chart with Date as the dimension and Sum([Enrollment Count]) should do the trick.

Hope this helps,

Jason

Re: Forecasting with selections

Wed, 14 Sep 2011 07:00:32 GMT

Hi Jason,

The trendline option at the bottom of the expression tab gives the trend only for the dates for which data exist. Is there anyway to get these trendlines to go ahead and plot for future dates(dates for which data does not exist) too?

Regards,

Rajiv

Forecasting with selections

Wed, 14 Sep 2011 07:23:18 GMT

Hi,

Qlikview do not gererate data. In General we make forecasting data and use that with Actual Data for comparison.

If you check the forecast checkbox in AXIS tab you will see the trendline little long which is forecast but no dimension or expression value will be shown.

Reg,

Shubhu

Re: Forecasting with selections

Wed, 14 Sep 2011 08:56:59 GMT

Hi,

>>Qlikview do not gererate data.

Correct. But I'm sure we can make it generate data based on past data.

>> In General we make forecasting data and use that with Actual Data for comparison.

We make forecasting data.. How do you do that in qlikview. Thats what I was asking in the question above.

>>If you check the forecast checkbox in AXIS tab you will see the trendline little long which is forecast but no dimension or expression value will be shown.

Checking the forecast checkbox in AXIS tab is not giving any forecast. Not sure why.

Forecasting with selections

Jason_Michaelides — Thu, 15 Sep 2011 16:25:54 GMT

A quick search on the community found this:

http://community.qlik.com/message/2023#2023

It suggests looking at the linest_xxxx functions in QlikView. I'm not statistician so it's all greek to me but might help?

Jason

Forecasting with selections

Fri, 16 Sep 2011 07:14:09 GMT

How about letting QV generate new data in the load script with a concatenated resident load where the date is generated for the coming 14 days - wouln't that solve both wishes?

Regards Robert.

Forecasting with selections

Fri, 16 Sep 2011 08:37:15 GMT

Hi Robert,

Could you give a sample app on how you intend to do it, on the data provided above??

Regards,

Rajiv

Forecasting with selections

Fri, 16 Sep 2011 11:53:57 GMT

I've done a document that seems to work, but how do I attach it to this thread? I can only attach images and videos in this editor??

Forecasting with selections

Miguel_Angel_Baeyens — Fri, 16 Sep 2011 11:57:27 GMT

Robert,

When adding or editing the post, click on the "Use advanced editor" in the right upper part of the text editor. In the lower part of the new dialog you will see the "Add File" button.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Forecasting with selections

Fri, 16 Sep 2011 12:01:58 GMT

Ok, thanks. Here it is.

Re: Forecasting with selections

Fri, 16 Sep 2011 13:53:58 GMT

Hi Robert,

Thanks for taking time out and building the application. I just had a quick glance at it(lots of office work currently) and it looks to work.

Will go through the solution once I am little more free.

Thanks again. I'm sure it ll be of help

Cheers,

Rajiv

Re: Forecasting with selections

Lee_Matthews — Mon, 13 May 2013 01:18:37 GMT

Hi Robert

Thanks for the great example. I have applied this to the projections section of a How To app I am working on. I added another expression to show the forecast adjusted for the deviation from the linear progression from 12 months ago. This essentially takes into account monthly seasonality, albeit using only the months in the prior year as the source of the seasonality. The app is attached if anyone is interested and a screen shot is below.

Although this is just sample data, it somewhat highlights how simple statistical calculations do not result in useful forecasts - there are too many other factors that need to be considered as impacting the results from year to year. It would be nice to do a formula that averaged the deviation across multiple years....but I think as time passes the older data becomes less and less relevant. So such analysis would be mostly of academic interest rather than of much use in a business scenario.

For anyone interested in doing forecasts like this, note the extra work I did in the masterCalendar:

- Add extra future periods to your calendar to allow you to forecast forward

- Add some sort of flag to highlight the periods that are future periods. This allows you to use set analysis to isolate the values you want to calculate the linear progression from, (otherwise the future months with zero sales seem to get included in the calcs and the forcast line is distorted). I made sure the current month was treated as a forecast month so that the incomplete sales data is ignored for the calculations.