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

    Count number of months

    Fernando Toro

      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

           

          StoreOpenSince.PNG

          • 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

                 

                1.png

                 

                a:

                load [SALE DATE], MONTH inline [

                SALE DATE, MONTH, OPERATION MONTH

                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

                ];

                 

                b:

                NoConcatenate load

                  [SALE DATE],

                  MONTH,

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

                          Peek(MonthSequence),

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

                Resident a

                order by [SALE DATE];

                 

                DROP Table a;