Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: how to Get month and Qtr information

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

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

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

Highlighted
anbu1984
Honored Contributor III

Re: how to Get month and Qtr information

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

Re: how to Get month and Qtr information

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
Honored Contributor

Re: how to Get month and Qtr information

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

Re: how to Get month and Qtr information

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

Re: how to Get month and Qtr information

Hey Ali,

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

Regards,

Viresh

ali_hijazi
Honored Contributor

Re: how to Get month and Qtr information

ali_hijazi
Honored Contributor

Re: how to Get month and Qtr information

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;