Help on ways to improve Master Calendar

[#1]

Hi Everyone. I'm trying to modify my existing Master Calendar to show dates based on available data instead of showing dates of current calendar.

For example if i my data only contains datas of year 2012 the dashboard should only show 2012 as the maximum date but it is showing 2013 as the year.

My Qlikview document have normal date formats from SQL database and also a combination of Year and quarter for data from Excel spredsheet.

Please suggest on how i can do this or are there any other approach to this issue. Below are the code for master calender:

Calendar:  
LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(MonthEnd(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) AS PostingDate,  
Date(TempDate) AS InvestedDate,
Date(TempDate) AS InvestedDate2,  
// Standard Date Objects
Day(TempDate) AS PostingDayOfMonth,  
//Month
Month(TempDate) AS PostingMonth,  
//Quarter 
'Q' & Ceil(Month(TempDate)/3) AS PostingQuarter,  
Ceil(Month(TempDate)/3) AS PQuarter,
//Year
Year(TempDate) AS PostingYear,  
// Combo Date 
Year(TempDate) & ' ' & Month(TempDate) AS YearMonth, 
'Q' & Ceil(Month(TempDate)/3) & ' ' & Year(TempDate)as YearQuarter,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS PostingYearAndQuarterStaff,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS QuarterYear,
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS PostingYearAndQuarter 
//Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS PostingYearAndQuarter
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());  

[Vegar]

What about changing the vDateMax variable from Floor(MonthEnd(Today())) to the max(Date) in your data set? No need to store up on dates that you don't use anyhow.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

[#3]

Hi Vegar,

I have tried your suggestion but the calendar failed to load and gave an error as below.

[Vegar]

You probaby need to get the maxDate into a variable.

LOAD

Max(Date) as maxdate

Resident

YourTransactionTable;

let vMaxDate = peek('maxdate');

[#5]

Hi Vagar,

I tried all the below but still no luck

LET vDateMax = Floor(MonthEnd(Max(Date)));

LET vDateMax = Floor(MonthEnd(Peek(PostingDate)));   

LET vDateMax = Peek(PostingDate);

LET vDateMax = Max(Date);

[Anonymous]

I also struggle to get a satisfactory start and end date to my master calendar.

The peek doesn't work for me, presumably because I add the dates from mutliple types or records into my linked date field. As I understand it the peek function only works as expected if the field is ordered numerically, as opposed to in load order.

Logically speaking, I think setting a variable with max(Date) should work.

LET varMaxDate = "=max(TransDate)";

However, though that expression works when set in the variables on the front end, it returns nothing when done in the script editor.

Jonathan

[Vegar]

Vivek Nalliah wrote:

Hi Vagar,

I tried all the below but still no luck

LET vDateMax = Floor(MonthEnd(Max(Date)));

LET vDateMax = Floor(MonthEnd(Peek(PostingDate)));   

LET vDateMax = Peek(PostingDate);

LET vDateMax = Max(Date);

You should use ' ' when calling Peek. Peek('PostingDate'). If it still doesn't work I suggest you post a sample qvw-file and source-file that shows what you are trying to do.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

[er_mohit]

try this

// Calender

LET vDateMin = Num(MakeDate(2006,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 $(vDateToday)-$(vDateMin);

//EXIT Script;

LOAD

//

Date(TempDate,'DD-MM-YYYY')as TempDate,

////Day(TempDate) AS CalendarDayOfMonth

//RESIDENT TempCalendar ORDER BY TempDate ASC;

//

//DROP Table TempCalendar;

//

//

Day(TempDate) AS CalendarDayOfMonth,

YearName(TempDate,0,2)as FiscalYear,

Year(TempDate) AS YTD,

Month(TempDate) AS CalendarMonthName,

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

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

MonthEnd(TempDate) as LMTD,

QuarterEnd(TempDate) as LQTD,

//Date(Date#(TempDate,'DD-MM-YYYY')+10)as Current_date,

YearEnd(TempDate) as LYTD

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[#9]

Hu Jonbroughavone, I have tried your suggestion but it did not work.

[#10]

H Er, I tried the suggestion above but it made no changes. The year is still showing as 2003