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.

       

       

       

      Antonius.

       

       

      ( I am using 3 tables Sales, Invoice, and Companies)

       

      QuartersMap:

      MAPPING LOAD

      rowno() as Month,

      'Q' & Ceil (rowno()/3) as Quarter

      AUTOGENERATE (12);

       

      //Calendar for Close date or Start

      Temp:

      Load

                     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;

       

      TempCalendar:

      LOAD

                     $(varMinDate) + Iterno()-1 As Num,

                     Date($(varMinDate) + IterNo() - 1) as TempDate

                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

       

      MasterCalendar:

      Load

                     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;