Time

[#1]

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

[israrkhan]

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...

[#3]

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)

[qlikpahadi07]

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

[#5]

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

[#6]

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.

[#7]

Hi ,

I want to group the data on a chart.

[#8]

in the calculated  dimension put the expression: year ([TRANS DATE])

[#9]

Thanks