topic Re: Errors when Trying to use Master Calendar in QlikView

Errors when Trying to use Master Calendar

prees959 — Mon, 25 Jun 2018 11:38:48 GMT

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...

Re: Errors when Trying to use Master Calendar

YoussefBelloum — Mon, 25 Jun 2018 11:57:56 GMT

Hi,

try changing just the vStartDate line to this:

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

Re: Errors when Trying to use Master Calendar

rajeshqvd — Mon, 25 Jun 2018 12:58:18 GMT

Hi Phil,

Please share full script

Re: Errors when Trying to use Master Calendar

captain89 — Mon, 25 Jun 2018 14:35:46 GMT

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;

Re: Errors when Trying to use Master Calendar

prees959 — Wed, 27 Jun 2018 07:33:40 GMT

Thanks!

Can you explain what this difference means?

Re: Errors when Trying to use Master Calendar

YoussefBelloum — Wed, 27 Jun 2018 08:29:26 GMT

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.

Re: Errors when Trying to use Master Calendar

prees959 — Wed, 27 Jun 2018 17:41:31 GMT

Hi Yousseff,

Thanks for the explanation - very much appreciated!

Phil