13 Replies Latest reply: Mar 23, 2015 4:30 AM by sarah karamsi RSS

    Forcasting in Qlikview

    Kaushik Solanki

      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
          Robert Svebeck

          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
              Kaushik Solanki

              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
                  Scott Morris

                  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
                    Robert Svebeck

                    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
                        Debbie Pyykkonen

                        Robert

                         

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

                          • Re: Forcasting in Qlikview
                            Robert Svebeck

                            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
                                Debbie Pyykkonen

                                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.

                          • Forcasting in Qlikview
                            Raghavendra Suryakumar

                            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
                                Debbie Pyykkonen

                                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
                                    Robert Svebeck

                                    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
                                        sarah karamsi

                                        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

                                    • Re: Forcasting in Qlikview
                                      Julien Bruchon

                                      Hi,

                                       

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

                                       

                                      Thanks

                                       

                                      Julien