Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi, to do it, let see this post, it work,
Master Calendar Data
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
HI Padila
I have my date field coming from the excel sheet , How do i do it ?
Hi John, take a look at this document from Henric:
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;
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 .
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.
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.
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;
Looking at the error, it seems that your [Order Entry Date] might not be read as date. Check these blogs
and make sure that your dates are read properly by QlikView