topic Re: Time in QlikView

Time

Wed, 02 Oct 2013 08:16:48 GMT

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

israrkhan — Wed, 02 Oct 2013 08:50:46 GMT

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

Wed, 02 Oct 2013 10:01:39 GMT

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

qlikpahadi07 — Wed, 02 Oct 2013 11:16:05 GMT

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

Wed, 02 Oct 2013 11:29:26 GMT

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

Wed, 02 Oct 2013 13:11:01 GMT

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.

Re: Time

Wed, 02 Oct 2013 13:18:21 GMT

Hi ,

I want to group the data on a chart.

Re: Time

Wed, 02 Oct 2013 13:24:44 GMT

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

Re: Time

Wed, 02 Oct 2013 14:16:46 GMT

Thanks