topic Creating master calendar in QlikView

Creating master calendar

deepakqlikview_123 — Fri, 06 Sep 2013 05:08:45 GMT

Hi,

Can you please guide us how to create master calendar in qlikview.

Thanks,

Re: Creating master calendar

er_mohit — Fri, 06 Sep 2013 05:18:35 GMT

Try this code on your script and reload it

// Calender

LET vDateMin = Num(MakeDate(2008,1,1));

LET vDateMax = Floor(DayEnd(Today()));

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,

num(WeekDay(Today()))as ppp,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'W' & Ceil(Week(TempDate)/8) as Fiscalweek,

Date(Date#('Mar Week 5','MMM WWW D'),'DD/MM/YYYY') AS Sunday,

Year(TempDate) AS CalendarYear,

Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3), Year(TempDate) & Ceil(Month(TempDate)/3)) as YearQtr,

'FY' & Right( Year(TempDate),2) as YearQtrfy,

// 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(day(TempDate)) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

Month(TempDate)&' '& 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,

'Sun ' & DayStart(WeekStart(TempDate) + 3) as CalendarSunday

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: Re: Creating master calendar

rwunderlich — Fri, 06 Sep 2013 05:25:56 GMT

Or use Qlikview Components http://qlikviewcomponents.org.

CALL Qvc.CalendarFromField('OrderDate')

-Rob

Re: Creating master calendar

Fri, 17 Jan 2014 05:52:49 GMT

Dear Mohit,

This is very helpful to me also.. this what i want too.

Re: Creating master calendar

jagan — Fri, 17 Jan 2014 06:09:45 GMT

Hi Sakthivel,

Try like this

CalendarMaster:

LOAD

Date(InvoiceDate) AS InvoiceDate,

Year(InvoiceDate) AS Year,

'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,

Month(InvoiceDate) As Month,

Day(InvoiceDate) As Day,

Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(InvoiceDate) AS MinDate,

Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

In the above script, replace 'Invoice' with your table name and InvoiceDate with your date field.

Hope this helps you.

Regards,

Jagan.

Re: Creating master calendar

Fri, 17 Jan 2014 06:40:41 GMT

Hi Deepak ,

Try this..

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;

Regards,

Vali Khan.

Re: Creating master calendar

Mon, 23 Jun 2014 16:18:21 GMT

where did the used this following syntax?

CALL Qvc.CalendarFromField('OrderDate')

my script is :

sheet1:

LOAD ReceivedDate,

Centre,

Centre_id,

Footfall_in,

Total_sales

FROM

(biff, embedded labels, table is [Table1$]);

thanks in advance