Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Month(Date(DATE#(YourDateField,'YYYYMMDD'))) as Month
Year(Date(DATE#(YourDateField,'YYYYMMDD'))) as Year
'Q'& Ceil(Month(Date(DATE#(YourDateField,'YYYYMMDD')))/3) as Quarter
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
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
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; |
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
Hey Ali,
This is really nice. How to make Nov as my first month instead of Jan.
Regards,
Viresh
this link would help
http://qlikcommunity.qliktech.com/docs/DOC-7094
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;