Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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

anbu1984
Not applicable

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

its_anandrjs
Not applicable

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

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;
I can walk on water when it freezes
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

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

Re: how to Get month and Qtr information

this link would help

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

I can walk on water when it freezes
ali_hijazi
Not applicable

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;

I can walk on water when it freezes