topic calender value in QlikView

calender value

Anonymous — Sun, 16 Jan 2011 08:32:08 GMT

I am trying to create a master calender for my data ,

I have a table in which time_stamp is the date field, the code i am having for master calender is given below , my qlikview table is based of on a sql query select statement , if i run the sql query , i get the chart display with a graph , based on my time_stamp values but as there are some dates missing in the time_stamp , i thought of getting the master calender to get all dates displyed inll formats , and for the dates on which there is no data the graph shows blank ..

But when i use the date values from master calender in the graph , it does not seem as all the metrics are calculated based on the time_stamp

How do i assign time_stamp to this calender ,

Calendar:

LET

vDateMin = Num(MakeDate(2009,1,1));

LET

vDateMax = Floor(MonthEnd(Today()));

LET

vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin)

+ RowNo() - 1 AS DateNumber

Date

($(vDateMin) + RowNo() - 1) AS Time_stamp

AUTOGENERATE

WHILE

$(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date

(Time_stamp) AS CalendarDate

// Standard Date Objects

Day

(Time_stamp) AS CalendarDayOfMonth

WeekDay

(Time_stamp) AS CalendarDayName

Week

(Time_stamp) AS CalendarWeekOfYear

Month

(Time_stamp) AS CalendarMonthName

'Q'

& Ceil(Month(Time_stamp)/3) AS CalendarQuarter

Year

(Time_stamp) AS CalendarYear

// Calendar Date Names

WeekName

(Time_stamp) as CalendarWeekNumberAndYear

MonthName

(Time_stamp) as CalendarMonthAndYear

QuarterName

(Time_stamp) as CalendarQuarterMonthsAndYear

// Start Dates

DayStart

(Time_stamp) as CalendarDayStart

WeekStart

(Time_stamp) as CalendarWeekStart

MonthStart

(Time_stamp) as CalendarMonthStart

QuarterStart

(Time_stamp) as CalendarQuarterStart

YearStart

(Time_stamp) as CalendarYearStart

// End Dates

DayEnd

(Time_stamp) as CalendarDayEnd

WeekEnd

(Time_stamp) as CalendarWeekEnd

MonthEnd

(Time_stamp) as CalendarMonthEnd

QuarterEnd

(Time_stamp) as CalendarQuarterEnd

YearEnd

(Time_stamp) as CalendarYearEnd

// Combo Date Examples

'Q'

& Ceil(Month(Time_stamp)/3) & '/' & Year(Time_stamp) AS CalendarQuarterAndYear

Year

(Time_stamp) & '/' & 'Q' & Ceil(Month(Time_stamp)/3) AS CalendarYearAndQuarter

'Wed '

& DayStart(WeekStart(Time_stamp) + 3) as CalendarWednesdays

RESIDENT

TempCalendar ORDER BY Time_stamp ASC;

DROP

TABLE

TempCalendar;

LET

vDateMin = Num(MakeDate(2000,1,1));

LET

vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET

vDateToday = Num(Today())

;

calender value

Bill_Britt — Sun, 16 Jan 2011 20:38:39 GMT

The below is based on a Field "OrderDate" in the Orders table
The TempCalendar is used to generate all the date between the values
VarMinDate and VarMaxDate.

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));
LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));
LET vToday = num(today());

TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<=
$(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS OrderDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
applymap('Quarters_Map', num(month(TempDate)),
null()) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS
CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

calender value

Anonymous — Sun, 16 Jan 2011 20:56:28 GMT

Does the ordetdate field has time value in it ? is it a datetime field?

calender value

Anonymous — Sun, 16 Jan 2011 21:10:20 GMT

I get an script error saying :

Field not found - <<=>
TempCalendar:
LOAD
+IterNo()-1 AS Num,
Date(+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE +IterNo()-1<=

Here is my script, i just added the tempcalender block to my exiting script which worked fine :

LET varMinDate = Num(Peek('TIME_STAMP', 0, 'RumiUtilization'));
LET varMaxDate = Num(Peek('TIME_STAMP', -1, 'RumiUtilization'));
LET vToday = num(today());

TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<=
$(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS TIME_STAMP,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
applymap('Quarters_Map', num(month(TempDate)),
null()) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS
CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

RumiUtilization:

LOAD

RUMI_DEVICE,
PROJECT,LOCATION,TIME_STAMP,Util_rate;

sql select ---- // all my sql select conditions

outer join(RumiUtilization) // loading from excel

LOAD

FROM
C:\Users\user\Desktop\test.xlsx
(ooxml, embedded labels, table is Location_ALL);

outer join(RumiUtilization)

LOAD

FROM
C:\Users\user\Desktop\test..xlsx
(ooxml, embedded labels, table is Project_ALL);