4 Replies Latest reply: Aug 29, 2011 11:39 AM by John Witherspoon RSS

    Previous 'n' months data calculation



      My requirement is to show previous 'n' months data in a chart.

      There should be a list box with choices as 1, 2, 3, ...10; and depending on user selection that much previous months data with respect to current date should get displayed.

      I have read all posts about rolling 3 months or rolling 13 months data and that hardcoding works just fine.

      But here I dont want to hardcode values, instead user should be able to select how many previous months data he wishes to see.

      Please help me out.

        • Re: Previous 'n' months data calculation
          Goran Korsgren



          As always there is more than one way to do it.


          You can try this:

          1. Create two variables, ie vMonthsInput and vCalculatedDate
          2. Create an Input Box for vMonthsInput
          3. Add two event triggers on vMonthsInput, one on OnChange and one on OnInput
            1. They should have the same action: "Set Variable" and you should set the variable vCalculatedDate to: =AddMonths(today(),-vMonthsInput )
          4. Use this set expression in the chart: {<Date={">$(vCalculatedDate)"}>}




          This will select Dates that are within vMonthsInput months from today.


          If you want to select Dates starting from the first of the month that was x months before today, you have to adjust the calculation in 3.1 above.



          • Re: Previous 'n' months data calculation
            John Witherspoon

            The attached example lets you select what rolling period to use for each month using a slider.  The selection when it comes up includes 0, 1 and 2 months back, so it is a rolling 3 months average including the current month.  Want a rolling 12 months average?  Drag the right side of the slider over to 12.  Want to exclude the current month, so it's a rolling previous 12 months?  Drag the slider one to the right so you're including months 1-13 back.

            • Re: Previous 'n' months data calculation

              Hi John,


              I have personnel edition of qlikview, so I cannot open your file




              Currently I am showing data on lat 13 Month's month end in chart. I want to make the number of months dynamic.

              When I use calculations suggested by you with today() it just works fine. But when I use it with Max(Date), it displays nothing.

              I want to use that calculation with Max(Date).

              Also I am not much familier with adding events. Please help me out.

              (In my database, date is in 'YYYY-MM-DD' format and I have qlikview 9.)

                • Re: Previous 'n' months data calculation
                  John Witherspoon

                  rohitsant87 wrote:


                  I have personnel edition of qlikview, so I cannot open your file


                  OK, here's the script:


                  LOAD *
                  ,date(monthstart(Date),'MMM YYYY') as MonthYear
                  date(date#(20080101,'YYYYMMDD')+floor(rand()*1000)) as Date
                  ,ceil(rand()*100) as Revenue
                  AUTOGENERATE 500

                  [Date Linkage]:
                  LOAD DISTINCT MonthYear
                  RESIDENT [Main]
                  LEFT JOIN ([Date Linkage])
                  LOAD MonthYear as AsOfMonthYear
                  RESIDENT [Date Linkage]
                  INNER JOIN ([Date Linkage])
                  LOAD *
                  ,round((AsOfMonthYear-MonthYear)/30.436875) as MonthsBack
                  RESIDENT [Date Linkage]
                  WHERE MonthYear <= AsOfMonthYear


                  For the chart:


                  Dimension  = AsOfMonthYear
                  Expression = sum(Revenue)/count(MonthsBack)


                  And beneath it there's a slider:


                  Field = MonthsBack
                  Multi Value
                  Static Step = 1