Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

How to create Quarters from date field?

Hi,

I have my date field, and I also have a Month Year field that I created from the MonthStart() function.

I have 3 years of data, 2012-2015.

But how would I go about creating Quarters for each year so it recognizes the months and the year for each quarter?

1 Solution

Accepted Solutions
Highlighted
Master III
Master III

Re: How to create Quarters from date field?

Hi,

Hope this will help u:

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -1)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

DATE_CALENDAR:
LOAD
     CALENDAR_DATE AS %KEY_CALENDAR_DATE,
     DATE(CALENDAR_DATE) AS CALENDAR_DATE,
     YEAR(CALENDAR_DATE) AS CALENDAR_YEAR,
     DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS CALENDAR_MONTH,
     NUM(MONTH(CALENDAR_DATE)) AS CALENDAR_MONTH_NUM,
     NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS CALENDAR_YEAR_MONTH_NUM,
     DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS CALENDAR_WEEK_ORDER,
     DAY(CALENDAR_DATE) AS CALENDAR_DAY,
     DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS CALENDAR_WEEK_DAY,
     'Q' &
CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,
     DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
          NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
     DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
          YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00')) AS CALENDAR_WEEK;
LOAD
     (
$(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

SET S_DATE_START=;
SET S_DATE_END=;




View solution in original post

9 Replies
Highlighted

Re: How to create Quarters from date field?

You don't have date field in your database. If you do, then you can do this:

'Q', Ceil(Month(DateField)/3) as Quarter,

'Q', Ceil(Month(DateField)/3) & '-' & Year(DateField) as QuarterYear,

Highlighted
Champion III
Champion III

Re: How to create Quarters from date field?

chk dis

Re: Dates

Highlighted
Creator II
Creator II

Re: How to create Quarters from date field?

='Q' & ceil(month(Date)/3) as Quarter

Highlighted
Master III
Master III

Re: How to create Quarters from date field?

Hi,

Hope this will help u:

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -1)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

DATE_CALENDAR:
LOAD
     CALENDAR_DATE AS %KEY_CALENDAR_DATE,
     DATE(CALENDAR_DATE) AS CALENDAR_DATE,
     YEAR(CALENDAR_DATE) AS CALENDAR_YEAR,
     DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS CALENDAR_MONTH,
     NUM(MONTH(CALENDAR_DATE)) AS CALENDAR_MONTH_NUM,
     NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS CALENDAR_YEAR_MONTH_NUM,
     DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS CALENDAR_WEEK_ORDER,
     DAY(CALENDAR_DATE) AS CALENDAR_DAY,
     DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS CALENDAR_WEEK_DAY,
     'Q' &
CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,
     DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
          NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
     DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
          YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00')) AS CALENDAR_WEEK;
LOAD
     (
$(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

SET S_DATE_START=;
SET S_DATE_END=;




View solution in original post

Highlighted
MVP
MVP

Re: How to create Quarters from date field?

There are also functions for creating quarter fields available similar to MonthStart(DateField) and MonthName(DateField) functions:

QuarterName(date [, shift = 0 [, first_month_of_year = 1]])

Returns a display value showing the months of the quarter (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp with the first millisecond of the first date of the quarter. Shift is an integer, where the value 0 indicates the quarter which contains date. Negative values in shift indicate preceding quarters and positive values indicate succeeding quarters. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Examples:

quartername ( '2005-10-29' ) returns 'Oct-Dec 2005' with an underlying numeric value corresponding to '2005-10-01 00:00:00.000'

quartername ( '2005-10-29', -1 ) returns 'Jul-Sep 2005' with an underlying numeric value corresponding to '2005-07-01 00:00:00.000'

quartername ( '2005-10-29', 0, 3 ) returns 'Sep-Nov 2005' with an underlying numeric value corresponding to '2005-09-01 00:00:00.000'

QuarterStart(date [, shift = 0 [, first_month_of_year = 1]])

Returns a value corresponding to a timestamp with the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the quarter which contains date. Negative values in shift indicate preceding quarters and positive values indicate succeeding quarters. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Examples:

quarterstart ( '2005-10-29' ) returns '2005-10-01' with an underlying numeric value corresponding to '2005-10-01 00:00:00.000'

quarterstart ( '2005-10-29', -1 ) returns '2005-07-01' with an underlying numeric value corresponding to '2005-07-01 00:00:00.000'

quarterstart ( '2005-10-29', 0, 3 ) returns '2005-09-01' with an underlying numeric value corresponding to '2005-09-01 00:00:00.000'

Highlighted
Not applicable

Re: How to create Quarters from date field?

Hi,

You can create quarter (your field) as below

   Dual('Q' & Ceil(Month(TIME_ID)/3), Ceil(Month(TIME_ID)/3)) //here Your field name is TIME_ID

Thanks

Reddy Kishore

Highlighted
Creator II
Creator II

Re: How to create Quarters from date field?

Just go through the Ceil () function provided in QLik manual.

it will help you and also increase your knowledge.

Regards,

mahamed

Highlighted
Partner
Partner

Re: How to create Quarters from date field?

Hi Sunny,

Thanks for this.

The only issue I have is that the Quarters are not sorting in order, I get Q1 2016, Q1 2015,  Q2 2015, Q3 2015, Q4 2015.

How do I go about creating Quarters that are sortable in order?

I cant load a sort order inline table since im creating new years all the time...

Highlighted

Re: How to create Quarters from date field?

May be this:

Dual('Q', Ceil(Month(DateField)/3) & '-' & Year(DateField), QuarterName(DateField)) as QuarterYear,