0 Replies Latest reply: Oct 11, 2012 1:08 AM by anthony678 RSS

    Setting "parameter" using calendar for financial report.

      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

      AUTOGENERATE (12);


      //Calendar for Close date or Start



                     min(CloseDate) as minDate,

                     max(CloseDate) as maxDate



      Resident InvoiceHeader;


      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

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;