Master Calendar Help

[#1]

Hi,

I'm currently using the following script to create a Master Calendar within my Dashboard:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

               min(actual_date) as minDate,

               max(actual_date) as maxDate

Resident MyTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

let vYesterday = Date(varMaxDate);

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

               TempDate AS CALL_DATETIME,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Is it possible to set the CurYTDFlag so that it runs from April to March?

Also, can the Quarters be reset so the run Q1 = Apr-May-Jun, Q2 = Jul-Aug-Sep, Q3 = Oct-Nov-Dec, Q4 = Jan-Feb-Mar?

Also, can anyone help on setting a Previous 30 Days Flag, a Previous 90 Days Flag a Previous 180 Days Flag?

Any help and advice greatly appreciated!

Many thanks,

Tom

[jonathandienst · Accepted solution]

Hi

I have adapted some code from my Calendar QVD generator that does what you are looking for:

//------------------------------------------------------------------------------------------
// Fiscal year parameters
//------------------------------------------------------------------------------------------
Set zGLYearStartMonth = 4; // Financial year start month (1 = same As cal year start)
Set zLastDOM = 31; // Last day of financial month (31 = end of cal month)

Let zCurrentYear = Year(Today(0));
Let vCurrentGLYear = zCurrentYear + If (zGLYearStartMonth = 1 Or Month(Today()) < zGLYearStartMonth, 0, 1);

  ..
  ..

//------------------------------------------------------------------------------------------
// Load Master Calendar
//------------------------------------------------------------------------------------------

MasterCalendar:
Load *,
  // Fiscal year fields - Fiscal Quarter
  Dual('Q' & Ceil(GLPeriodM / 4), Ceil(GLPeriodM / 4)) As GLQuarter,

  // Current fiscal year and last fiscal year flags
  If(GLYearOffset = 0, 1, 0) As CurYTDFlag,
  If(GLYearOffset = -1, 1, 0) As LastYTDFlag
;
Load *,
  // Fiscal year fields - Fiscal Year
  Year + If($(zGLYearStartMonth) <= 1 Or Month < $(zGLYearStartMonth), 0, 1) As GLYear,

  // Fiscal year fields - Fiscal Period
  If (Month >= $(zGLYearStartMonth),
  If(Day > $(zLastDOM), Month - $(zGLYearStartMonth) + 2, Month - $(zGLYearStartMonth) + 1),
  If(Day > $(zLastDOM), Month + (12 - $(zGLYearStartMonth) + 2), Month + (12 - $(zGLYearStartMonth) + 1))) As GLPeriodM,

  // Fiscal year fields - GLYearOffset
  If (CalMonth < $(zGLYearStartMonth),
  CalYear,
  CalYear + 1) - $(vCurrentGLYear) As GLYearOffset,

  // Aging flags
  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 30, 1, 0) As [Previous 30 Days Flag],
  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 90, 1, 0) As [Previous 90 Days Flag],
  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 180, 1, 0) As [Previous 180 Days Flag]
;
Load
  TempDate AS CALL_DATETIME,
  week(TempDate) As Week,
  Year(TempDate) As Year,
  Month(TempDate) As Month,
  Day(TempDate) As Day,
  //YeartoDate(TempDate)*-1 as CurYTDFlag,
  //YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
  inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
  date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
  WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

The variables are parameters that set the fiscal periods.

HTH

Jonathan

[yduval75]

You can use

YeartoDate(TempDate, 4)*-1 as CurYTDFlag,

[its_anandrjs]

Hi,

For CurrentYTD Flag you can change like

YeartoDate(AddMonths(TempDate,-3))*-1 as CurYTDFlag,

And for Quarter start from the Apr

ApplyMap('QuartersMap', month(AddMonths(TempDate,-3)), Null()) as Quarter,

Regards

Anand