topic Forcasting in Qlikview in QlikView

Forcasting in Qlikview

kaushiknsolanki — Tue, 04 Oct 2011 11:11:28 GMT

Hello Experts,

Can anyone tell me about forcasting in Qlikview.

Is it possible..? If yes then how to implement it and also provide me documents for the same.

Regards & Thanks,

Kaushik Solanki

Forcasting in Qlikview

Tue, 04 Oct 2011 12:02:56 GMT

Forecasting can be so many things depending on industry and terminology. Can you descibe what you mean and what you want to do or what your expected result whould be?

Are you aiming to create a forecast from Qlikview based on previous transactions, or are you loading a forecast and what to visualize this against actual transactions?

Regards
Robert

Forcasting in Qlikview

kaushiknsolanki — Tue, 04 Oct 2011 12:12:04 GMT

Hi,

Yes I am aiming to create a forecast based on the previous transactions. But I am unaware of the forecasting algorithms and I want to know is there any functionality in Qlikview which i am unaware of which can help me to obtain the forecasting.

Any information on these will be a grateful help.

Regards,

Kaushik Solanki

Forcasting in Qlikview

Tue, 04 Oct 2011 13:18:39 GMT

We do a lot of forecasting at the company where I work and I have yet to see a built in QV function that would calculate forecasting. We just have the forecast elements created on a separate table on the database and link by a common key and bring that into QV.

Forcasting in Qlikview

Wed, 05 Oct 2011 10:44:39 GMT

Besides normal math functions, there's a whole bunch of statistical aggregation functions in Qlikview. Look at page 374 to 380 in the Reference Manual. (in the script expression section)

The reference manual is stored in your Qlikview installation folder, like "C:\Program Files\QlikView\Documentation"

If you want to generate a forecast, it is in my experience best to do this in the load script.

This is how I've done it previously.

1. Load the source data into a transaction table.
2. Resident this loaded data with the appropriate expressions into a new forecast table.
3. Save the forecast table into a new QVD.

Regards
Robert

Forcasting in Qlikview

Tue, 25 Oct 2011 21:38:50 GMT

Robert

Do you have an example of the script used to create the forecast table that you can share?

Forcasting in Qlikview

raghavsurya — Wed, 26 Oct 2011 05:30:36 GMT

Hi Kaushik, I have created a forecasted Profit and Loss Account and Balance sheet as well using past year's data and Input fields/Input boxes to predict current year's forecasts. Do let me know if you are looking at any specific model on forecasting. Regards, Raghav

Re: Forcasting in Qlikview

RSvebeck — Wed, 26 Oct 2011 06:30:50 GMT

Hi. Here is a extremely simple example.

Best Regards
Robert Svebeck
(I was was forced to create a new acount because it was not possible to change mail for some reason...)

Re: Forcasting in Qlikview

Wed, 26 Oct 2011 15:15:43 GMT

Thank you robert. This is helpful but what I am trying to do is to calculate a forecast for the future months based on the prior month value

If Sept we sold 2000 units and the user input 10% Month over Month Growth then October would generate 2200 as forecast.

The user would need have the option of inputting a Month over Month % growth

I tried using the rangesum(above(sum())) function in my pivot object but the rowno() is set to 1 for all columns so the rangesum(above()) is not working

I also tried using set analysis to sum for the prior month IF the sum of sales for that month is 0 and that did not work either. i.e. =sum({<MonthsFromCurrentMonth={'-1'},[Organizational Roll-up]={'Premier'}>}[Activity Count]) but this results in the current month for each column only. Keep in mind my pivot is reversed. The expressions are the rows and the dimensions are the columns. This, I am assuming, is the reason that the rowno() is set to 1 for each month column.

Re: Forcasting in Qlikview

Wed, 26 Oct 2011 20:00:29 GMT

I tried using the columnno() and the below but same issue, I get 0 for all months

=rangesum(before(sum(),1))

Re: Forcasting in Qlikview

RSvebeck — Wed, 26 Oct 2011 21:49:39 GMT

So you basically want the forecast to be based always on the last sold month, ignoring seasonal changes etc?

Re: Forcasting in Qlikview

RSvebeck — Sat, 29 Oct 2011 11:48:57 GMT

Hi all

The "Before" function is only valid in pivot charts, in all other charts it will return NULL, which is why you get zero values in your columns.

I'm not sure of the best approach to do a a month over month. One way I've have done is to use macros, in this example I tried a different approach using rank() together with getpossiblecount() to figure out how to multiply the growthfactor.

In my attached example, I have 4 different ways of cacluating a forecast for next 12 months.

Best Regards,

Robert Svebeck

Re: Forcasting in Qlikview

Wed, 13 Jun 2012 16:13:29 GMT

Hi,

I would be interested in having a glance on your file. May you please make it available ?

Thanks

Julien

Re: Forcasting in Qlikview

Anonymous — Mon, 23 Mar 2015 08:30:06 GMT

Hi Robert,

Your example has turned out to be pretty helpful for me. i was wondering if you could assist me in figuring out how to do your forecast for more than one year.

Say for example i had Data for 2010 (july-dec) and 2011 (jan -dec)

How would I show values for the latest year (i.e max(year)) and then show a forecast for the next year?

I cant seem to figure out how to show the forecast line graphs onto the extended x axis but only for one single year. my values give me the current sales and a forecast for ALL my years .

Please Help.

Thanks

Sarah