Errors when Trying to use Master Calendar

[prees959]

Hi,

Im trying to use the following master calendar :

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

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 Calendar Month

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

All I really need to do add a Fiscal year to my data.  But when I use this script only some of my records have a Fiscal Year:

Can anyone help on this?  In my dataset im using  date([DATE],'DD/MM/YYYY') as Date, to ensure I'm getting the correct format...

[YoussefBelloum · Accepted solution]

Hi,

try changing just the vStartDate line to this:

LET vStartDate = Num(YearStart(Today(), -2));

[rajeshqvd]

Hi Phil,

Please share full script

[captain89]

Hi Phil,

Try changing vStartDate using a min function.

In this way you can set the min Date of your fact table as the first day of the master calendar

Temp:

load min(Date) as  minDate,

Today() as maxDate

resident yourfacttable;

LET vStartDate = Num(Peek('minDate', 0, 'Temp'));

LET vEndDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

[prees959]

Thanks!

Can you explain what this difference means?

[YoussefBelloum]

you're generating your Date and FiscalYear fields based on your (vStartDate and VEndDate) variables.

on vStartDate you used this: Num(YearStart(Today(), -1)); ==> this will give you 2017 and not 2016.. so for 2016 dates you are not goiing to generate your FiscalYear values.

[prees959]

Hi Yousseff,

Thanks for the explanation - very much appreciated!

Phil