Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
Showing results for 
Search instead for 
Did you mean: 
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


//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



  date($(vMinDate) + rowno() - 1) as Date


  $(vMaxDate) - $(vMinDate) + 1;

//Create date dimensions



  right(WeekName(Date,0,-2),2) -

      right(WeekName(Fiscal_Month_Start_Date,0,-2),2) + 1 as Fiscal_WeekNoInMonth




  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,


      &'-'& '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,


      &'-'& 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,


      &'-'& 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

See this blog post: Fiscal Year

talk is cheap, supply exceeds demand

View solution in original post

2 Replies

See this blog post: Fiscal Year

talk is cheap, supply exceeds demand

View solution in original post


Hi Bobbydave,

see below post:

QlikView How To (or Tips & Tricks) Application

by Lee Matthews


Offsetting the number of months to start the Fiscal Calendar, and other tricks