Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Calendar

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

9 Replies
Highlighted
Creator
Creator

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

Highlighted

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

View solution in original post

Highlighted
Creator III
Creator III

HI Padila

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

Highlighted
Specialist III
Specialist III

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;

Highlighted
Creator III
Creator III

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 .

Highlighted
Creator
Creator

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.

Highlighted
Creator III
Creator III

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.

Highlighted
Creator II
Creator II

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;

Highlighted

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