Calendar

[smilingjohn]

Hi All,

Iam trying to generate the master calendar , but its not working , Can please spome one look into this ?

PLease find the attachment !.

I referd all the community post , I have a column called Date in my excel sheet , I tried all byut its not working .

Can some one help me out please

[uroboros]

Hi, to do it, let see this post, it work,
Master Calendar Data

[sunny_talwar · Accepted solution]

See if the attached file helps. Although I believe you need to implement a master calendar as well as Master Time Table: The Master Time Table

[smilingjohn]

HI Padila

I have  my date field coming from the excel sheet , How do i do it ?

[miguelbraga]

Hi John, take a look at this document from Henric:

The Master Calendar

Or take a look at the regular Master Calendar template and try to acomodate to your solution:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

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

AUTOGENERATE (12); 

Temp: 

Load 

               min(OrderDate) as minDate, 

               max(OrderDate) as maxDate 

Resident Orders; 

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

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

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 OrderDate, 

               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;

[smilingjohn]

Thanks a lot sunny , But how do i add week and YTD fiscal year ,.

If you see there seems to be gap in between , the fiscal date is showing -- for actual date w, can you please explain me why is so ?

Please find the attachment .

[uroboros]

It work's equals, you have a date on your field
descompose it in years, months, or like you wanted; and create a new table with this data; i Will work
Something like this:

facts:

load

field0 as id (like a PK)

field1 as information (as an example)

field2 as date

from [exceldocument.xlsx]; (it is an example)

calendar:

id,

year(date) as year,

month(date) as month

resident facts;

drop fields date;

And voaila! you have a master calendar.

[smilingjohn]

HI Padila,

The attachment sent by Sunny talwar is working , But when i copy the master calendar data in my script and try to run its not working . I dontknow what trick is there behind it . I just wanted to add this YTD fiscal month and year.

[Anonymous]

Hi Sunny,

I am getting the below when ever I ran this query. Can you help me here?

Script line error:

LOAD Date( + RangeSum(Peek('RowNum'), 1) - 1) AS [Order Entry Date],

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

AutoGenerate vEndDate - vStartDate + 1

Query I ran is :

SET vFiscalYearStartMonth = 4;

DateRange:
LOAD Min([Order Entry Date]) AS MinDate,
Max([Order Entry Date]) AS MaxDate
Resident Prediction;

LET vStartDate = Peek('MinDate');
LET vEndDate = Peek('MaxDate');

DROP TABLE DateRange;

FiscalCalendar:
LOAD *,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
  Dual(Text(Date(MonthEnd([Order Entry Date]), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD *,
Year([Order Entry Date]) AS Year, // Standard Calendar Year
  Month([Order Entry Date]) AS Month, // Standard Calendar Month
  Date(MonthEnd([Order Entry Date]), 'MMM') AS MonthName,  // Standard Calendar Month Name
  Dual('Q' & Ceil(Month([Order Entry Date])/3), Ceil(Month([Order Entry Date])/3)) AS Quarter,  // Standard Calendar Quarter
  Mod(Month([Order Entry Date]) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month
  YearName([Order Entry Date], 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year
LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS [Order Entry Date],
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;

[sunny_talwar]

Looking at the error, it seems that your [Order Entry Date] might not be read as date. Check these blogs

Get the Dates Right

Why don’t my dates work?‌

and make sure that your dates are read properly by QlikView