12 Replies Latest reply: May 12, 2013 9:27 PM by Lee Matthews RSS

    Forecasting with selections

      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

          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

          • Forecasting with selections
            Shubhasheesh Chakraverty

            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

            • Forecasting with selections

              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

                  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

                      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 de Arce

                          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

                            Ok, thanks. Here it is.

                              • Re: Forecasting with selections

                                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

                                  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.

                                   

                                  Projection2.jpg

                                   

                                  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.