Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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());
1. Use Qlikview Components http://qlikviewcomponents.org
CALL Qvc.CalendarFromField(PostingDate)
or
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
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
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
Hi Vegar,
I have tried your suggestion but the calendar failed to load and gave an error as below.
You probaby need to get the maxDate into a variable.
LOAD
Max(Date) as maxdate
Resident
YourTransactionTable;
let vMaxDate = peek('maxdate');
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);
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
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
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
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;
Hu Jonbroughavone, I have tried your suggestion but it did not work.
H Er, I tried the suggestion above but it made no changes. The year is still showing as 2003