Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Converting Dates to Month to Qtr. to Year

Hi I have a column with Dates in my Data, I want further column from it which shows Month , Qtr, years. I am using below code:

[DISP_DATE],

  Week ([DISP_DATE]) as [DISP_Week],

  Month([DISP_DATE]) as [DISP_Month],

  Year([DISP_DATE]) as [DISP_Year],

  'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

I am able to compile code without an error , but when I try to use Week , Month , year column in my dashboard there is no value, can someone please help me in this regard, I am very new to Qlik and not sure what error I am making .

Also, DISP_DATE columns have values like 01-Sep-2016 , 03-Sep-2016 .... 02-Oct-2016

13 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Data:

LOAD

*,

  Week ([DISP_DATE]) as [DISP_Week],

  Month([DISP_DATE]) as [DISP_Month],

  Year([DISP_DATE]) as [DISP_Year],

  'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

LOAD

Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],

*

FROM DataSource;

Regards,

Jagan.

sunny_talwar

Like Jagan pointed out, there seems to be interpretation problem. QlikView doesn't always understand your date. One way to handle this without using Date#() would be to define your date format in the environmental variable

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 ₽;-# ##0,00 ₽';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MMM-YYYY';

SET TimestampFormat='DD-MMM-YYYY h:mm:ss[.fff]';

SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';

SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';

SET LongMonthNames='Январь;Февраль;Март;Апрель;Май;Июнь;Июль;Август;Сентябрь;Октябрь;Ноябрь;Декабрь';

SET LongDayNames='понедельник;вторник;среда;четверг;пятница;суббота;воскресенье';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='ru-RU';

and by doing this, you would be able to avoid the use of preceding load and your current script should work

[DISP_DATE],

  Week ([DISP_DATE]) as [DISP_Week],

  Month([DISP_DATE]) as [DISP_Month],

  Year([DISP_DATE]) as [DISP_Year],

  'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

But many times we run into an issue where we pull data from multiple sources with different formats, that's when Date#() in handy and it makes sense to set the environmental variable to a format which you would like to see your date in the front end of the application.

Read here to learn about Dates in QlikView

Why don’t my dates work?

Get the Dates Right

Anonymous
Not applicable
Author

Hello Jagan, thanks for the guidance , I tried to put below code in my script, it changes the format of my data from 03-Sep-2016 to 03/08/2016 that is fine, but Week, Qtr and Year columns are still blank.


Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

  //[DISP_DATE],

     Week ([DISP_DATE]) as [DISP_Week],

     Month([DISP_DATE]) as [DISP_Month],

     Year([DISP_DATE]) as [DISP_Year],

     'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

Anonymous
Not applicable
Author

Sunny, thanks for the reply, I have made changes in environmental variable as you suggested for both Date and Time Stamp and also put Date#() function, but still my Week, Month and Qtr column is blank


Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

  //[DISP_DATE],

     Week ([DISP_DATE]) as [DISP_Week],

     Month([DISP_DATE]) as [DISP_Month],

     Year([DISP_DATE]) as [DISP_Year],

     'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

jagan
Luminary Alumni
Luminary Alumni

Hi Ankit,

The script should work, did you noticed the second load statement(Preceding Loading) in my script.  I am not sure you did the preceding loading.  Please find below script highlighted in Bold text. 

Data:

LOAD

*,

  Week ([DISP_DATE]) as [DISP_Week],

  Month([DISP_DATE]) as [DISP_Month],

  Year([DISP_DATE]) as [DISP_Year],

  'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

LOAD

Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],   // Formatting the date

*

FROM DataSource;

Regards,

Jagan.

Anonymous
Not applicable
Author

Hi Jagan, I am loading the data from single excel worksheet, pasting the entire script below for your reference so that you can see whats wrong I am doing

Thx

Ankit

[Export Worksheet]:

LOAD

[REGION_DESC],

  [TERRITORY_DESC],

  [MD_CUST_ID],

  [MD_CMID],

  //[MD_FIRST_NAME],

  //[MD_LAST_NAME],

    [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

  [PATIENT_CMID],

  [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

  [ABC_ID],

  [PATIENT_INITIALS],

  [PATIENT_BIRTH_DATE],

  [PATIENT_GENDER],

  [PATIENT_DISPOSITION],

  [REFERRAL_DATE],

  [GRADUATION_DATE],

  [ASAP_DISPOSITION],

    //[DISP_DATE], [ This is the Original column from Excel]

     Week ([DISP_DATE]) as [DISP_Week],

     Month([DISP_DATE]) as [DISP_Month],

     Year([DISP_DATE]) as [DISP_Year],

     'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],

     Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

    [DISP_AGE],

  [DISP_REASON],

  [SR_STATUS],

  [SR_SOURCE],

  [PROCUREMENT],

  [AVONEX_SPP],

  [DECISION_MAKER],

  [TREATMENT_CHOICE],

  [ABM_ALERT_CODE],

  [MD_OPT_OUT],

  [ACCESS_ENROLLED_FLAG],

  [CURR_THERAPY],

  [CURR_THERAPY_CONFIRM_DT],

  [PREV_THERAPY],

  [INS_PRVDR1_CMID],

  [INS_PRVDR1_NAME],

  [INS_PRVDR1_PHONE],

  [INS_PRVDR2_CMID],

  [INS_PRVDR2_NAME],

  [INS_PRVDR2_PHONE],

  [ENROLLED_ATS_FLAG],

  [SHIP_DATE],

  [SA_SR_ID],

  [SOURCE_SR_ROW_ID],

  [TRAINING_REQUIRED],

  [TRAIN_BY_NURSE],

  [REFUSED_TRAINING_FLG],

  [COPAY_STATUS],

  [ZIN_REMS_ID],

  [PATIENT_REMS_STATUS],

  [PATIENT_REMS_STATUS_DATE],

  [MD_REMS_STATUS],

  [MD_REMS_STATUS_DATE],

  [JOIN_TERR],

  [TERR_DESC]

FROM [lib://AttachedFiles/disp_export.xlsx]

(ooxml, embedded labels, table is [Export Worksheet]);

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

[Export Worksheet]:

LOAD

*,

Week ([DISP_DATE]) as [DISP_Week],

     Month([DISP_DATE]) as [DISP_Month],

     Year([DISP_DATE]) as [DISP_Year],

     'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];

LOAD

[REGION_DESC],

  [TERRITORY_DESC],

  [MD_CUST_ID],

  [MD_CMID],

  //[MD_FIRST_NAME],

  //[MD_LAST_NAME],

    [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

  [PATIENT_CMID],

  [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

  [ABC_ID],

  [PATIENT_INITIALS],

  [PATIENT_BIRTH_DATE],

  [PATIENT_GENDER],

  [PATIENT_DISPOSITION],

  [REFERRAL_DATE],

  [GRADUATION_DATE],

  [ASAP_DISPOSITION],

    //[DISP_DATE], [ This is the Original column from Excel]    

     Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

    [DISP_AGE],

  [DISP_REASON],

  [SR_STATUS],

  [SR_SOURCE],

  [PROCUREMENT],

  [AVONEX_SPP],

  [DECISION_MAKER],

  [TREATMENT_CHOICE],

  [ABM_ALERT_CODE],

  [MD_OPT_OUT],

  [ACCESS_ENROLLED_FLAG],

  [CURR_THERAPY],

  [CURR_THERAPY_CONFIRM_DT],

  [PREV_THERAPY],

  [INS_PRVDR1_CMID],

  [INS_PRVDR1_NAME],

  [INS_PRVDR1_PHONE],

  [INS_PRVDR2_CMID],

  [INS_PRVDR2_NAME],

  [INS_PRVDR2_PHONE],

  [ENROLLED_ATS_FLAG],

  [SHIP_DATE],

  [SA_SR_ID],

  [SOURCE_SR_ROW_ID],

  [TRAINING_REQUIRED],

  [TRAIN_BY_NURSE],

  [REFUSED_TRAINING_FLG],

  [COPAY_STATUS],

  [ZIN_REMS_ID],

  [PATIENT_REMS_STATUS],

  [PATIENT_REMS_STATUS_DATE],

  [MD_REMS_STATUS],

  [MD_REMS_STATUS_DATE],

  [JOIN_TERR],

  [TERR_DESC]

FROM [lib://AttachedFiles/disp_export.xlsx]

(ooxml, embedded labels, table is [Export Worksheet]);

sunny_talwar

Is you date in DD/MM/YYYY format or DD-MMM-YYYY? You are using DD/MM/YYYY for DISP_DATE?

Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],

miskin_m
Partner - Creator
Partner - Creator

Hi,

Try this one

[Export Worksheet]:

LOAD

[REGION_DESC],

  [TERRITORY_DESC],

  [MD_CUST_ID],

  [MD_CMID],

  //[MD_FIRST_NAME],

  //[MD_LAST_NAME],

    [MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],

  [PATIENT_CMID],

  [PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],

  [ABC_ID],

  [PATIENT_INITIALS],

  [PATIENT_BIRTH_DATE],

  [PATIENT_GENDER],

  [PATIENT_DISPOSITION],

  [REFERRAL_DATE],

  [GRADUATION_DATE],

  [ASAP_DISPOSITION],

    //[DISP_DATE], [ This is the Original column from Excel]

     Week (date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Week],

     Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Month],

     Year(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Year],

     'Q-' & Ceil(Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))/3) as [DISP_Quater],

    date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],

    [DISP_AGE],

  [DISP_REASON],

  [SR_STATUS],

  [SR_SOURCE],

  [PROCUREMENT],

  [AVONEX_SPP],

  [DECISION_MAKER],

  [TREATMENT_CHOICE],

  [ABM_ALERT_CODE],

  [MD_OPT_OUT],

  [ACCESS_ENROLLED_FLAG],

  [CURR_THERAPY],

  [CURR_THERAPY_CONFIRM_DT],

  [PREV_THERAPY],

  [INS_PRVDR1_CMID],

  [INS_PRVDR1_NAME],

  [INS_PRVDR1_PHONE],

  [INS_PRVDR2_CMID],

  [INS_PRVDR2_NAME],

  [INS_PRVDR2_PHONE],

  [ENROLLED_ATS_FLAG],

  [SHIP_DATE],

  [SA_SR_ID],

  [SOURCE_SR_ROW_ID],

  [TRAINING_REQUIRED],

  [TRAIN_BY_NURSE],

  [REFUSED_TRAINING_FLG],

  [COPAY_STATUS],

  [ZIN_REMS_ID],

  [PATIENT_REMS_STATUS],

  [PATIENT_REMS_STATUS_DATE],

  [MD_REMS_STATUS],

  [MD_REMS_STATUS_DATE],

  [JOIN_TERR],

  [TERR_DESC]

FROM [lib://AttachedFiles/disp_export.xlsx]

(ooxml, embedded labels, table is [Export Worksheet]);