Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

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
sunny_talwar

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
uroboros
Creator
Creator

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

sunny_talwar

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
Specialist
Specialist
Author

HI Padila

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

miguelbraga
Partner - Specialist III
Partner - 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;

smilingjohn
Specialist
Specialist
Author

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

smilingjohn
Specialist
Specialist
Author

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
Not applicable

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