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

    Time

      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.

       

      regards

        • Re: Time
          Israr Khan

          hi,

           

          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
            Rakesh Prakash

            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);

                MinData:
                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;

                CALENDAR:
                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],

                       DEBTOR,

                       QUANTITY,

                       [SALES VALUE],

                       [COST OF SALES]

                  FROM

                  [test.xls]

                   

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