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

how to Get month and Qtr information

Hi Team,

I have a date field in YYYYMMDD format like 20140919.

Using the Left function i can get the year field. How to get the month field and quarter field.

My fist month should be November the last month should be October.

thanks a lot for your help.

Regards,

Viresh

8 Replies
MK_QSL
MVP
MVP

Month(Date(DATE#(YourDateField,'YYYYMMDD'))) as Month

Year(Date(DATE#(YourDateField,'YYYYMMDD'))) as Year

'Q'& Ceil(Month(Date(DATE#(YourDateField,'YYYYMMDD')))/3) as Quarter

anbu1984
Master III
Master III

Month(Date#(Date_Field,'YYYYMMDD')) -- Month

Ceil(Month(Date#(Date_Field,'YYYYMMDD'))/3) -- Quarter


Mid('20140919',5,2) -- Month

Ceil(Mid('20140919',5,2)/3) -- Qtr

its_anandrjs

Try with

Load

Month(Date(Date#(Mid(Datefield,5,2),'mm'),'mm') ) as Month

'Q' & Ceil (Month(Date(Date#(Mid(Datefield,5,2),'mm'),'mm') ) /3) as Quarter

From Source;

Regards

Anand

ali_hijazi
Partner - Master II
Partner - Master II

here is the code of a full calendar

StartYear = Year(Today()) -4;
YearsToGenerate=Year(Today()) - $(StartYear) +1;
UNQUALIFY *;
//Look through number of years defined above
for i = 1 to YearsToGenerate;
let curYear = StartYear+(i-1);
let StartDate = makedate(curYear);  //Returns first day of year
let YearEnd = YearEnd(StartDate);   //Returns last day of year
let EndWeek = WeekEnd(StartDate);
let DayEndWeek = day(EndWeek);
let WeekStart = date(EndWeek);
//use if week starts on Saturday let WeekStart = date(EndWeek - 1);
//Calculate number of days in the year
let TotalDays =(YearEnd-StartDate)+2;
//Create a temporary calendar
TempCalendar:
load recno()*$(i) as DateKey,
'$(StartDate)'+recno()-1 as PERIOD_DATE
autogenerate(TotalDays-1);
   
Next
//Building the master calendar with most date dimensions
MasterCalendar:
load
num(daystart(PERIOD_DATE)) as PERIOD_DATE,
Week(PERIOD_DATE) as Week,
DateKey,
Year(PERIOD_DATE) as Year,
Month(PERIOD_DATE) as Month,
Num(Month(PERIOD_DATE)) as MonthNum,
Day(PERIOD_DATE) as Day,
'Q' & Ceil(Month(PERIOD_DATE)/3) as Quarter,
Year(PERIOD_DATE) & '-' & 'Q' & Ceil(Month(PERIOD_DATE)/3) as QUARTER_YEAR,
Year(AddMonths(PERIOD_DATE,-3)) & '-' & 'Q' & ceil(Month(AddMonths(PERIOD_DATE,-3))/3) AS PREVIOUS_QUARTER_YEAR,
Year(PERIOD_DATE) & ' - S' & Ceil(Month(PERIOD_DATE)/6) as Season,
if(MOD(Month(PERIOD_DATE),6) =0,1,0) AS IS_LAST_MONTH_OF_SEASON,
Month(PERIOD_DATE) & '-' & Day(PERIOD_DATE) as MonthDay,
Date(MonthStart(PERIOD_DATE), 'MMM-YYYY') as MonthYear,
Week(PERIOD_DATE) as CalendarWeek,
Week(PERIOD_DATE) & '-' & Year(PERIOD_DATE) as WeekYear,
WeekDay(PERIOD_DATE) as WeekDay
resident TempCalendar  order by PERIOD_DATE Asc;
Drop table TempCalendar;
I can walk on water when it freezes
Not applicable
Author

Hi Manish,

Thanks for the reply. Its working fine but my first quarter should consists of Nov , Dec and Jan instead of normal quarter Jan, Feb and Mar.

Is this possible?

Regards,

Viresh

Not applicable
Author

Hey Ali,

This is really nice. How to make Nov as my first month instead of Jan.

Regards,

Viresh

ali_hijazi
Partner - Master II
Partner - Master II

this link would help

http://qlikcommunity.qliktech.com/docs/DOC-7094

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II

SET vFiscalYearStartMonth = 4;//you need to update this value to suit your needs

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

I can walk on water when it freezes