topic Re: How to create a master calendar and graphs using master calendar in QlikView

How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 11:39:58 GMT

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.

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 11:54:02 GMT

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

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 12:37:59 GMT

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.

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 12:44:44 GMT

Yes just copy & paste above code..

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

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 12:49:55 GMT

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.

Re: How to create a master calendar and graphs using master calendar

nizamsha — Fri, 28 Jun 2013 12:54:29 GMT

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;

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 12:58:06 GMT

It shows an error. please see attached

Re: How to create a master calendar and graphs using master calendar

Fri, 28 Jun 2013 13:11:06 GMT

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.

Re: How to create a master calendar and graphs using master calendar

Mon, 01 Jul 2013 06:05:42 GMT

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

Re: How to create a master calendar and graphs using master calendar

Thu, 06 Feb 2014 18:02:15 GMT

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