Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This videos show how to create a Master Date Calendar in QlikView. The script mentioned in the video is below.
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;
Can you post yout script?
Rob below is the calendar script where it is failing. I also attached the
Error screenshot.
Simon
//*****Fiscal Year Quarter Map*****
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q2
2, Q2
3, Q2
4, Q3
5, Q3
6, Q3
7, Q4
8, Q4
9, Q4
10, Q1
11, Q1
12, Q1
];
LET varMinDate = Num(MakeDate(2010,10,1)); //
first date
LET varMaxDate = Floor(num(Today())); // will find
the last record
LET varToday = Num(today());
// Finds todays date
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE 1
While $(varMinDate)+IterNo()-1<= $(varMaxDate);
//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS PostingDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as
Quarter,
yearname ( TempDate, 0, 10 ) as FiscalYear,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Month(TempDate)&'-'&right(year(TempDate),10) as
FiscalMonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as
RC12,
inyeartodate(TempDate, $(varToday), 0 [,
first_month_of_year = 10]) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1 [,
first_month_of_year = 10]) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
On Wed, Jun 13, 2012 at 6:06 AM, Rob Wunderlich <
This line you have a double comma
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,
This is a typo and not in the script.
On Wed, Jun 13, 2012 at 11:39 AM, Felim Shanaghy <
The code below works, its related to : -
inyeartodate(TempDate, $(varToday), 0 [,
first_month_of_year = 10]) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1 [,
first_month_of_year = 10]) * -1 AS LastYTDFlag
The above are incorrectly formatted
//*****Fiscal Year Quarter Map*****
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q2
2, Q2
3, Q2
4, Q3
5, Q3
6, Q3
7, Q4
8, Q4
9, Q4
10, Q1
11, Q1
12, Q1
];
LET varMinDate = Num(MakeDate(2010,10,1)); //first date
LET varMaxDate = Floor(num(Today())); // will find the last record
LET varToday = Num(today()); // Finds todays date
//*************** Temporary Calendar ***************
TempCalendar:
LOAD $(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE 1
While $(varMinDate)+IterNo()-1<= $(varMaxDate);
//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS PostingDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
yearname ( TempDate, 0, 10 ) as FiscalYear,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Month(TempDate)&'-'&right(year(TempDate),10) as FiscalMonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
inyeartodate(TempDate, $(varToday), 0, 10) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1, 10) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Hi Felim, That makes sense and works. Any idea how can I create a fiscal
week, or match week to fiscal month?
On Wed, Jun 13, 2012 at 12:06 PM, Felim Shanaghy <
Hi Simon is I am uncertain how your fiscal periods work, in the past I have used calendars stored in spreadsheets dictating the calendar, the reason I done this at the time was that the fiscal week would actually change, so lets say if your fiscal week 1 starts on the first monday of October, we don't generally know what day that is. I am sure we could do it in code if you give us the business rules for the fiscal periods
For some reason when I use the loop: AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
it does not show some months in the correct order whenever I select them. To solve that I used the next line instead of the while loop:
$(varMaxDate) - $(varMinDate) + 1;
Anyway, this is such a good Calendar example
Attach I have qvw that has inline table containing 2 different dates.
I have dealt with creating master calander from one date, however creating from 2 dates is confusing me.
example qvw can be found below.
Hey Josh, I just noticed a bug with the script. If a week goes over into the new year it gets the incorrect year/week assigned.
Take for example Jan 1, 2012
in your code Week(TempDate) & '-' & Year(TempDate) as WeekYear,
The week returns 52, because it is the 52nd week of 2011, week 1 of 2012 starts on the following Monday. The year argument returns 2012. so the end result is "52-2012" which is really the end of 2012 instead of 2011.
To get around this I use the weekstart argument for the weeks so it always looks at the Monday of the week.
e.g. Week(weekstart(TempDate)) & '-' & Year(weekstart(TempDate)) as WeekYear,
Cheers,