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: 
bobbydave
Creator III
Creator III

Calendar that starts from February

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);

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See this blog post: Fiscal Year


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

See this blog post: Fiscal Year


talk is cheap, supply exceeds demand
jpenuliar
Partner - Specialist III
Partner - Specialist III

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