Hi everyone in Qlikview forum,
I am new in Qlikview, and I have few difficulties regarding calendar and creating financial report. Below I copy my code for master calendar for anyone to see and help me. I need to create financial report from August 2010 to July 2011. The date I am using is CloseDate from Invoice table. Now I want to be able set the report in year basis which is August 2010 to July 2011.
Second question, there are 18 organizations in my source file. However not all of them have Closedate that within the timeline of the financial report that I will make, how can I excluded them from the report? I don't want to display them in my report.
Oh yeah I am using Personal Edition so please don't send me qlikview file if you can copy it in your reply. or in another document (word or notepad).
Thank you very much.
( I am using 3 tables Sales, Invoice, and Companies)
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
//Calendar for Close date or Start
min(CloseDate) as minDate,
max(CloseDate) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate AS CreatedDate,
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)) & '-' & Year(weekstart(TempDate)) as WeekYear,
WeekDay(TempDate) as WeekDay
Order By TempDate ASC;
Drop Table TempCalendar;