4 Replies Latest reply: Jun 23, 2016 2:31 AM by Manuel Capella RSS

    Group Date Field Into Months

    Roy Morales

      Hi,

       

      I am using Qlik Desktop

       

      I have a field in my database that is formatted this way: MM/DD/YYYY

       

      For example:

       

      01/04/2016

      01/05/2016

      01/06/2016

      ...

      05/05/2016

       

      Is there a possible way to group everything in months so it will show on my PivotTable

       

      January 2016

      February 2016

      March 2016

       

      And under the month will be:

       

      January 2016

                01/04/2016

                01/05/2016

                01/06/2016

      February 2016

                02/01/2016

                02/02/2016

       

      Thank you very much.

        • Re: Group Date Field Into Months
          Marcus Sommer

          I think you should use a master-calendar: How to use - Master-Calendar and Date-Values which would give you all needed period-fields and a view similar to your requirement could you get if you the used a pivot-chart with MonthYear and Date as Dimensions.

           

          - Marcus

          • Re: Group Date Field Into Months
            Thirumala Dandamudi

            May be something as attached:

             

            Source_Data:

            Load * Inline [

            SalesDate,SalesVolume

            01/01/2016,1000

            01/02/2016,3000

            03/30/2016,2500

            03/31/2016,3100

            04/8/2016,6238

            04/10/2016,4816

            05/05/2016,7522

            ];

             

            NoConcatenate

            DailySales:

            Load date(Date#(SalesDate,'MM/DD/YYYY')) as SalesDate,

              SalesVolume

            Resident Source_Data;

             

            CalendarMaster:

            LOAD

              Date(SalesDate) AS SalesDate,

              Year(SalesDate) AS Year,

              Month(SalesDate)&'-' &Year(SalesDate) AS MonthYear,

              'Q' & Ceil(Month(SalesDate) /3) AS Quarter,

              Month(SalesDate) AS Month,

              Day(SalesDate) AS Day,

              Week(SalesDate) AS Week;

             

            Load

            Date(MinDate + Iterno() -1) AS SalesDate

            While (MinDate + Iterno() -1) <= Num(MaxDate);

             

            Load

            Min(Date(SalesDate, 'MM/DD/YYYY')) AS MinDate,

            Max(Date(SalesDate, 'MM/DD/YYYY')) AS MaxDate

            Resident DailySales;

             

            Drop Table Source_Data;

            • Re: Group Date Field Into Months
              Sunny Talwar

              You can either use MonthName() function: monthname ‒ QlikView. But this will not give you the full month name (unless you have specified full monthnames in the environmental variables in the script.

               

              Alternatively, you can try this: Date(MonthStart(Date), 'MMMM YYYY') as MonthYear

              MonthStart() is key here which will floor all your dates to 1st of the month and will associate all dates in a month to its MonthYear field.

               

              HTH

               

              Best,

              Sunny

              • Re: Group Date Field Into Months
                Manuel Capella

                HI Roy, you can use MonthName function:

                Monthname('23/05/2016') -> May 2016

                 

                Regards!