I have used qlikview for the past 6months but this is the first time im going to use a master calendar.
I do create a report on performance analysis of the company. i have lots tab in my script editor. Each tab is named after the department name.
One of the department is Purchase to Pay which has both purchasing and payment.
In the purchasing table, theres a date for PR Creation, PR Release Date, PO Creation Date, PO Approved Date and other non date data
In Payment, there is a date for Expense Transaction Date, Reimbursement Date, Expense Approved Date and other non date data.
I would like to create a graph for purchasing alone, that in a particular week or month how may PR were created and released and PO created and PO approved.
In Payment same thing, that in a particular month how many expenses incurred by the employees, paid by the accountant and approved by the manager.
Please help. I have been reading master calendar yet it bugs me so much and when i tried using it, it affects one or more of my tab which has dates i dont want to affect.
If u have one or more than one table ..create one "DATE" field..
Take Date From ur MainTable Create Master on that..
num(min(DATE)) AS MinDate,
num(max(DATE)) AS MaxDate
LET vMinnDate = peek('MinDate', 0, 'Caltemp');
LET vMaxxDate = peek('MaxDate', 0, 'Caltemp');
DROP TABLE Temp;
date($(vMinnDate) + rowno() - 1,'DD MMM YYYY') AS DATE,
year($(vMinnDate) + rowno() - 1) AS Year,
month($(vMinnDate) + rowno() - 1) AS Month,
date(monthstart($(vMinnDate) + rowno() - 1), 'MMM-YYYY') AS MonthYear,
week($(vMinnDate) + rowno() - 1) & '-' & year($(vMinnDate) + rowno() - 1) AS WeekYear,
vMaxxDate - vMinnDate + 1;
should i just copy paste this to the script editor?
theres an error when i tried.
The dates in purchasing is just in 1 table actually its just that its 4 or more dates in one table.
Yes just copy & paste above code..
Check 4th line...which table u want to resident and alos that table should have one "DATE" field.
Which date are u going to use for master calendar PO Creation Date, PO Approved Date,etc?
Take a one date(PO Creation Date) as key_date and with this key_date need to joined the Purchase table and Master date table like below
Load PO Creation Date as Key_date
,Year(Key_date) as Year
,Month(Key_date) as Month
,Yearweek(Key_date) as Yearweek
,Year(Key_date)&'-'&Month(Key_date) as Yearmonth
Now this table joined through Key_date field.
LOAD Min(SaleDate) as DMin,
Max(SaleDate) as DMax
first see from which table u r taking the date
thats the main one
LOAD Date($(vDayStart)+RowNo()-1) as TempDate
LOAD TempDate as SaleDate, //put the date field here from the table u r picking before
Month(TempDate) as Month,
Year(TempDate) as Year,
Week(TempDate) as Week,
WeekDay(TempDate) as WeekDay,
Month(TempDate)& '-' & Year(TempDate) as Period,
Year(TempDate)*100+Month(TempDate) as [Period(#)],
'Q'&Ceil(Month(TempDate)/3) as Quarter,
Year(YearName(TempDate,0,4)) as FiscalYear, //FISCAL CALCULATION
Dual(Month(MonthsStart(1,TempDate,0,4)),Mod((Month(TempDate)-4),12)+1) as FiscalMonth,
'Q'&Ceil(Dual(Month(MonthsStart(1,TempDate,0,4)),Mod((Month(TempDate)-4),12)+1)/3) as FiscalQuarter
//DUAL(Month(YourDate), MonthName(YourDate)) AS Month
//Month(MonthStart(MonthsName(1,TempDate,0,4),$(FisMonthNames))) as FiscalMonth
//Dual(Month(MonthsName(1,$(vDayStart),0,4))+RowNo()-1,Month(TempDate)+RowNo()-1) as FiscalMonth
//Month() as FiscalMonth,
//Week(FisDate) as FiscalWeek,
//Year(FisDate)*100+Month(FisDate) as Period
DROP Table Date_Temp;
DROP Table Temp_Date;
What i actually have in mind is that all the dates like PO Date PR date PO approved date will meet at a one common date.
PR number PR CreatedDate PO Date PO Approved Date
12345 Jan 4, 2013 Jan 16, 2013 Jan 17, 2013
12679 Jan 4, 2013 Jan 17, 2013 Jan 17, 2013
76865 Jan 5, 2013 Jan 18, 2013 Jan 21, 2013
I want a graph that the dimension is normal calendar date e.g. jan dates. then a bar for the no. of PR Created, a line for the PO Created Date and a symbol for PO approved date.
try this code
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(MakeDate(2030,12,31));
LET vDateToday = Num(Today());
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
//LET vDateMin = Num(MakeDate(2000,1,1));
//LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
//LET vDateToday = Num(Today());
What if there are no date fields but SQL "Timestamp" fields? It erros out... how can I convert that Timestamp field into a Date field