Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am creating a calendar but the start really should start from February or rather Q1 should start from February.
Q1 = Feb Mar Apr; Q2 May June July Q3 Aug Sept Oct Q4 Nov Dec Jan.
I was thinking to add Month(Date)+1 as Month but when looking at this in a list box, it will go from 2-13 where 13 doesnt really make sense as Jan.
I've tried setting Jan as the last month
SET MonthNames='Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec;Jan';
Is there a way of ensuring Q1/Q2/Q3/Q4 are in the correct order.
Right now Q1 is showing as Jan Feb Mar
Thanks
//Set max and min date for calendar that will be created
LET vMinDate = num(MakeDate(2010,1,1));
LET vMaxDate = num(today());
//Generate dates between min and max date
tmpGenerateCalendarDates:
LOAD
date($(vMinDate) + rowno() - 1) as Date
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;
//Create date dimensions
T_Calendar:
LOAD *,
right(WeekName(Date,0,-2),2) -
right(WeekName(Fiscal_Month_Start_Date,0,-2),2) + 1 as Fiscal_WeekNoInMonth
;
LOAD
Date,
Date as %Calendar,
Date as [Trans Date],
Date(Date,'DD/MM/YYYY') as Formatted_Date,
//Basic Date Dimensions
Year(Date) as Year,
// Increment the month number and start from two.
// From here, we need to apply #2 to start at February
//////////////////////////////////////////////////////
Month(Date)+1 as Month,
//////////////////////////////////////////////////////
Week(Date) as Week,
Day(Date) as Day,
'Q' & ceil(month(Date)/3) as Quarter,
DayName(Date) as DayName,
WeekName(Date) as WeekName,
Year(Date) &'-'& Week(Date) as YearWeek,
Year(Date) &'-'& num(Month(Date)) as YearMonth,
Year(Date) &'-'& Month(Date) as YearMonthName,
MonthName(Date) as Year_Month_Formatted,
Date(Date,'YYYYMM') as Calendar_Period,
//Year Flags
InYear(Date, $(vToday), 0) * -1 as CurrYrFlag,
InYear(Date, $(vToday), -1) * -1 as PrevYrFlag,
InYearToDate(Date, $(vToday), -0) * -1 as YTDFlag,
InYearToDate(Date, $(vToday), -1) * -1 as Prev_YTDFlag,
//Quarter Flags
InQuarter(Date, $(vToday), 0) * -1 as Curr_Quarter_Flag,
InQuarter(Date, $(vToday), -1) * -1 as Previous_Quarter_Flag,
InQuarterToDate(Date, $(vToday), 0) * -1 as QTDFlag,
InQuarterToDate(Date, $(vToday), -1) * -1 as Prev_QTDFlag,
//Month Flags
InMonth(Date, $(vToday), 0) * -1 as Curr_Month_Flag,
InMonth(Date, $(vToday), -1) * -1 as Prev_Month_Flag,
InMonth(Date, $(vToday), -1) * -1 as LastFullMonth_TY_Flag, //legacy flag name
InMonth(Date, $(vToday), -13) * -1 as LastFullMonth_LY_Flag, //legacy flag name
InMonthToDate(Date, $(vToday), 0) * -1 as MTDFlag,
InMonthToDate(Date, $(vToday), -1) * -1 as Prev_MTDFlag,
//Week Flags
InWeekToDate(Date, $(vToday), 0) * -1 as WTDFlag,
InWeekToDate(Date, $(vToday), -1) * -1 as Prev_WTDFlag,
//Rolling Periods
if(Date>=AddMonths(Today(),-3),1,0) as Last3Months_TY_Flag,
if(Date<=AddMonths(Today(),-12) and Date>=AddMonths(Today(),-15),1,0) as Last3Months_LY_Flag,
if(Date>=AddMonths(Today(),-12),1,0) as MAT, //rolling 12 months
if(Date<=AddMonths(Today(),-12) and Date>=AddMonths(Today(),-24),1,0) as [MAT-1], //rolling previous 12 months
//Fiscal Date Dimensions
ApplyMap('MapDateToFiscalYear',Date) as Fiscal_Year,
ApplyMap('MapDateToFiscalPeriod',Date) as Fiscal_Period,
// Added by CN 20140915
'Q' & ceil(num(ApplyMap('MapDateToFiscalPeriod', Date))/3) as Fiscal_Quarter,
ApplyMap('MapDateToFiscalYear',Date)
&'-'& 'Q' & ceil(num(ApplyMap('MapDateToFiscalPeriod', Date))/3) as Fiscal_YearQuarter,
ApplyMap('MapDateToFiscalYearPeriod',Date) as FiscalYearPeriod,
month(makedate(2012,ApplyMap('MapDateToFiscalPeriod',Date))) as Fiscal_Month,
month(makedate(2012,ApplyMap('MapDateToFiscalPeriod',Date))) & ' ' &
ApplyMap('MapDateToFiscalYear',Date) as Fiscal_Year_Month_Formatted,
ApplyMap('MapDateToFiscalYear',Date)
&'-'& num(month(makedate(2012,ApplyMap('MapDateToFiscalPeriod',Date))),'00') as Fiscal_Year_Month,
Date(ApplyMap('MapDateToFiscalPeriodStartDate',Date),'DD/MM/YYYY') as Fiscal_Month_Start_Date,
Date(ApplyMap('MapDateToFiscalPeriodEndDate',Date),'DD/MM/YYYY') as Fiscal_Month_End_Date,
right(WeekName(Date,0,-2),2) as Fiscal_Week,
ApplyMap('MapDateToFiscalYear',Date)
&'-'& right(WeekName(Date,0,-2),2) as Fiscal_Year_Week
Resident tmpGenerateCalendarDates;
Drop Table tmpGenerateCalendarDates;
Left Join (T_Calendar)
Load Fiscal_Year_Month, max(Fiscal_WeekNoInMonth) as Fiscal_MonthNoWeeks
Resident T_Calendar
group by Fiscal_Year_Month;
Store T_Calendar into [..\QVDs\T_Claims_Scorecard_Calendar.qvd](qvd);
//STORE T_Calendar INTO [..\QVDs\T_Calendar.qvd](qvd);
See this blog post: Fiscal Year
Hi Bobbydave,
see below post:
QlikView How To (or Tips & Tricks) Application
by Lee Matthews
http://community.qlik.com/docs/DOC-5486
Offsetting the number of months to start the Fiscal Calendar, and other tricks