Dated

[smilingjohn]

Hi All

I have a single table

In which there are 3 date fields which are

Entry Date

Collected Date

Sample Date

FInal Date

Among which i have used Entry date as Date  for Master Calendar ,

Now my question is if i want to use other date fields also in master calendar how do i use it

Iam using the below script for master calendar for the Entry Date . Which is working perfect and giving

me month, year quarter and so on . How do i use the above trhee date fields within the same master calendar ?

I have renamed Entry Date as Date

MaxMin:

LOAD Max(Date) as MaxDate,

  Min(Date) as MinDate

Resident ProductData;

SET vFiscalYearStartMonth = 4;

let vStartDate=Peek('MinDate');

let vEndDate=Peek('MaxDate');

FiscalCalendar:

LOAD

*,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal  Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr;  // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Thanks in advance

[jagan · Accepted solution]

Rename Date AS DateType

Load  [Final Date] as CanonicalDate, 'Final' as DateType

          Resident Prodcuts;