Master Calendar

[upaliwije]

Can someone help me  with script of master calendar giving dates,weeks,months,quarters and years

[#2]

This is a basic  calendars script...

MinMax:

LOAD

  Max([CreatedOn])  AS MaxDate,

  Min([CreatedOn])  AS MinDate

//  Num(MakeDate(2011,11,1)) as MinDate //use this if you want to limit the min date range  e.g from 1/7/2010 onwards

RESIDENT YourDataTable;

LET varMinDate = Num(Peek('MinDate',0,'MinMax'));

LET varMaxDate = Num(Peek('MaxDate',-1,'MinMax'));

LET varToday = num(today());

// Now create the master calendar

MasterCalendar:

LOAD

  *,

  Date([CreatedOn]) AS [ Created Date],

  Date([CreatedOn]) AS [Date],

  Year([CreatedOn]) AS [Year],

  'Q' & ceil(Month([CreatedOn])/3) AS [Quarter],

  Month([CreatedOn]) AS [Calendar Month],

  'FY' & Right(Year(AddMonths([CreatedOn],-6)),2) & '/' & Right(Year(AddMonths([CreatedOn],6)),2) AS [Financial Year],

// dual(text(Month([CreatedOn])),applymap('FiscalMonth_Map', text(Month([CreatedOn])))) AS [Financial Month],

// applymap('FinQuarter_map', Month([CreatedOn])) AS FinQtr,//orders the quarter to the Financial Year 1/7 30/6

// applymap('FiscalMonth_Map', Month([CreatedOn])) AS [Fin Month],//orders the quarter to the Financial Year 1/7 30/6

    Week([CreatedOn]) AS Week,

  Day([CreatedOn]) AS [Day],

  Weekday([CreatedOn]) AS WeekDay,

  Date(monthstart([CreatedOn]), 'MMM-YYYY') AS MonthYear,

        If([Start Date]>addmonths(Today(), -12), 1) as Rolling12MonthsFlag,

  Week([CreatedOn])&'-'&Year([CreatedOn]) AS WeekYear,

  date(daystart(weekend([CreatedOn],0,5)),'DD/MM/YYYY') as [Week Ending],

  inyeartodate([CreatedOn], $(varToday), 0) * -1 AS CurYTDFlag,

  inyeartodate([CreatedOn], $(varToday), -1) * -1 AS LastYTDFlag,

  InMonthToDate([CreatedOn], $(varToday), 0) * -1 as CurMTDFlag,

  InMonthToDate([CreatedOn], $(varToday), -1) * -1 as LastMTDFlag;

LOAD

  Date($(varMinDate) + RecNo() - 1) AS [CreatedOn]

AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);

DROP TABLE MinMax;

Cheers

Brett

[jagan]

Hi,

Try like this

CalendarMaster:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,    

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(InvoiceDate) AS MinDate,

    Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

In the above script, replace 'Invoice' with your table name and InvoiceDate with your date field.

Hope this helps you.

Regards,

Jagan.