Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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..
Try This...
Temp:LOAD
num(min(DATE)) AS MinDate,
num(max(DATE)) AS MaxDate
RESIDENT MainTable;
LET vMinnDate = peek('MinDate', 0, 'Caltemp');
LET vMaxxDate = peek('MaxDate', 0, 'Caltemp');
DROP TABLE Temp;
MCalender:LOAD
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,
AUTOGENERATE
vMaxxDate - vMinnDate + 1;
Regards-Bika
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.
Hi,
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
Purchase:
Load PO Creation Date as Key_date
,*
from Purchase.qvd;
Mastercalendar;
Load 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
resitant Purchase;
Now this table joined through Key_date field.
Regards,
Kabilan K.
Date_Temp:
LOAD Min(SaleDate) as DMin,
Max(SaleDate) as DMax
Resident MAIN;
first see from which table u r taking the date
thats the main one
LET vDayStart=Num(Peek('DMin',0,'Date_Temp'));
LET vDayEnd=Num(Peek('DMax',-1,'Date_Temp'));
LET vToday=Num(Today());
Temp_Date:
LOAD Date($(vDayStart)+RowNo()-1) as TempDate
AutoGenerate
$(vDayEnd)-$(vDayStart)+1;
Master_Calendar:
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
Resident Temp_Date;
DROP Table Date_Temp;
DROP Table Temp_Date;
It shows an error. please see attached
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.
For example
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
// Calender
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(MakeDate(2030,12,31));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
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,
YearName(TempDate,0,2)as FiscalYear,
// 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