8 Replies Latest reply: Oct 2, 2013 10:16 AM by Nompumelelo Manzini RSS


      Hi ,


      I am new to Qlikview . I would like to group my data by year . How can I do this? My data is from an excel spreadsheet.



        • Re: Time
          Israr Khan



          where do you want to group the data, in the script or in the chart.

          if you load data in qlikview, and use year as dimension, it will automatically group by year.


          let us know the details, what you want to achieve exactly...

          • Re: Time

            If you have date in a specific column, use the Year() function to get year. Use this as a dimension in chart or table


            Eg. Year(Date_Field)

            • Re: Time
              Rohit Koul

              Welcome to Click


              Simply Pull your data from Excel spread sheets and Year(Date field) as Year.

              Load *, Year( Data) as Year


              See the Attachment

              • Re: Time
                Claudio Oliveira

                Why do not you create a calendar where all your dates will be linked to information from the year, month, quarter, etc..



                LOAD Date(Max(Date_field)) as MaxBaseline
                FROM [yourfile.xlsx]
                ooxml, embedded labels, header is 9 lines, table is plan1);

                LOAD Date(Min(Date_Field)) as MinBaseline
                FROM [yourfile.xlsx]
                ooxml, embedded labels, header is 9 lines, table is plan1);

                let vMaxData = peek('MaxBaseline',0,'MaxData');
                let vMinData = peek('MinBaseline',0,'MinData');

                DROP Tables MaxData, MinData;

                Load  $(#vMinData) + RowNo() -1 as ID_Calendar,
                $(#vMinData) + RowNo() -1 as Data,
                Week($(#vMinData) + RowNo() -1) as Week,
                Year($(#vMinData) + RowNo() -1) as Year,
                Month($(#vMinData) + RowNo() -1) as Month,
                Day($(#vMinData) + RowNo() -1) as Day,
                WeekDay($(#vMinData) + RowNo() -1) as Day_week,
                      'T' &
                CEIL (Month($(#vMinData) + RowNo() -1)/3) AS Quarter,
                Date(MonthStart ($(#vMinData) + RowNo() -1), 'MMM-YYYY') as Month_year,
                Week ($(#vMinData) + RowNo() -1) & '-' & Year($(#vMinData) + RowNo() -1) as week_year
                AUTOGENERATE (vMaxData - vMinData + 1);


                • Re: Time

                  Thanks for the responses, I am trying to group the data on a chart


                  LOAD [TRANS CODE],

                      ([TRANS DATE]) ,

                       [ITEM CODE],



                       [SALES VALUE],

                       [COST OF SALES]




                  My date is stored in as a number in the excel spreadsheet i.e. 38912.