10 Replies Latest reply: Sep 22, 2015 3:27 PM by Massimo Grossi RSS

    Count number of months

      Hi,  I'm new in Qlikview and what I need to do is count numbers of month of operation for the different stores... How do I do that?... For example if a store started sale since July 2015 for July I must to count 1 and 2 for August... I want to do this creating a new dimension in Script Editor...


      Please help.

        • Re: Count number of months
          Paras Doshi

          There are two options here. You can use the script editor OR you can use SQL to add this field while loading the data. I prefer SQL so I am going to share that:


          (here's an example of SQL against MS SQL Server Database but you can adjust this based on your target database)


          select StoreOpenDate, DATEDIFF(mm,StoreOPenDate,GETDATE()) as OpenSinceNumOfMonths from StoreDimension



          • Re: Count number of months
            Jonathan Dienst

            Assuming you don't have the opening date, but you can get it from the first transaction date, you could do something like this:


            Join (Sales)

            LOAD Store,

              Min(Date) As OpenDate

              Month(Min(Date)) * 12 + Year(Min(Date)) As OpenMonthSequence,

            Resident Sales

            Group By Store;


            Now in the front end, in a table/chart with Store as a dimension use an expression like this for months of operation

              =(Month(Today()) * 12 + Year(Today())) - OpenMonthSequence


            • Re: Count number of months
              Jonathan Poole

              i can think of a couple ways to do this.


              You can do a resident load in the data load editor that loads all stores and does a month(min(OrderDate)) and year(min(OrderDate)) from the orders table.  That way you have the first month for each store in the data model and it will be relatively easy to get a count of months between that date and today's date.


              If not, i think its still possible with a more involved set analysis statement, but that depends on your data model structure. Anyway you can send sample data / model to build a sample? 

              • Re: Count number of months
                Massimo Grossi

                another option could be





                load [SALE DATE], MONTH inline [


                20150212, 02, 1

                20150212, 02, 1

                20150212, 02, 1

                20150213, 02, 1

                20150213, 02, 1

                20150219, 02, 1

                20150316, 03, 2

                20150327, 03, 2

                20150430, 04, 3

                20150522, 05, 3




                NoConcatenate load

                  [SALE DATE],


                  if(left([SALE DATE],6)=left(Peek('SALE DATE'),6),


                          Alt(Peek(MonthSequence),0)+1) as MonthSequence

                Resident a

                order by [SALE DATE];


                DROP Table a;