Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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;

Community Browser