Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gupta-anki
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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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

gupta-anki
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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

gupta-anki
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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.

gupta-anki
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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
Not applicable

Re: Converting Dates to Month to Qtr. to Year

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