Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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

View solution in original post

jpenuliar
Partner
Partner

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