Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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;

0 Replies