topic Re: Help on ways to improve Master Calendar in QlikView

Help on ways to improve Master Calendar

Mon, 14 Jan 2013 04:13:36 GMT

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

Re: Help on ways to improve Master Calendar

Vegar — Mon, 14 Jan 2013 06:37:56 GMT

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

Re: Help on ways to improve Master Calendar

Mon, 14 Jan 2013 07:11:44 GMT

Hi Vegar,

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

Re: Help on ways to improve Master Calendar

Vegar — Mon, 14 Jan 2013 09:20:48 GMT

You probaby need to get the maxDate into a variable.

LOAD

Max(Date) as maxdate

Resident

YourTransactionTable;

let vMaxDate = peek('maxdate');

Re: Help on ways to improve Master Calendar

Mon, 21 Jan 2013 09:39:26 GMT

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

Re: Help on ways to improve Master Calendar

Anonymous — Mon, 21 Jan 2013 09:50:42 GMT

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

Re: Help on ways to improve Master Calendar

Vegar — Mon, 21 Jan 2013 12:08:25 GMT

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

Re: Help on ways to improve Master Calendar

er_mohit — Mon, 21 Jan 2013 12:21:04 GMT

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;

Re: Help on ways to improve Master Calendar

Tue, 22 Jan 2013 03:32:47 GMT

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

Re: Help on ways to improve Master Calendar

Tue, 22 Jan 2013 03:34:54 GMT

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

Re: Help on ways to improve Master Calendar

rwunderlich — Tue, 22 Jan 2013 08:45:53 GMT

1. Use Qlikview Components http://qlikviewcomponents.org

CALL Qvc.CalendarFromField(PostingDate)

2. Use a preceeding load:

TempCalendar:

LOAD

Date(mindate + RowNo() - 1) AS TempDate

WHILE mindate +IterNo()-1<= maxdate;

LOAD

min(Date) as mindate

,max(Date) as maxdate

RESIDENT Sales

;

If you want to handle multiple fact dates see the tutorial at

http://robwunderlich.com/tutorials/

-Rob