How to create a master calendar and graphs using master calendar

[#1]

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.

[#2]

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

[#3]

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.

[#4]

Yes just copy & paste above code..

Check 4th line...which table u want to resident and alos that table should have one "DATE" field.

[#5]

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.

[nizamsha]

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;

[#7]

It shows an error. please see attached

[#8]

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.

[#9]

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()); 

[#10]

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