Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD,MTD,QTD calculation in scripts.

Hello All,

I am trying to implement backend scripting to create ytd, mtd and qtd calculation,

I am using PointInReporting.qvw from qlikview help for help but not getting the idea or flow of implementation.

Like i have a calender table:

Calendar:

LOAD

Date(TempDate,'DD/MM/YYYY') AS [Posting Date],

Day(TempDate) AS CalendarDayOfMonth,

Week(TempDate) AS CalendarWeekOfYear,

Text(Month(TempDate)) AS Month,

Date(TempDate,'MMMM') AS CalendarFullMonth,

Num#(Date(TempDate,'MM'),'00') AS CalendarMonthNum,

Year(TempDate) AS CalendarYear,

MonthName(TempDate) AS YearMonth,

YearName(TempDate, 0, 4) AS FiscalYear,

Left(YearName(TempDate, 0, 4), 4) AS ShortFiscalYear

RESIDENT TempCalendar

ORDER BY TempDate ASC;

where this calendar is linked to other table by Posting Date,

where the tab of Calender is prior to other table containing posting date.

Please provide me help regarding this.

Thanks and Regards,

1 Reply
Not applicable
Author

Hi,

     You can create calander in this way

LOAD

Date([Posting Date],'DD/MM/YYYY') AS [Posting Date],

Day([Posting Date]) AS CalendarDayOfMonth,

Week([Posting Date]) AS CalendarWeekOfYear,

Text(Month([Posting Date])) AS Month,

Date([Posting Date],'MMMM') AS CalendarFullMonth,

Num#(Date([Posting Date],'MM'),'00') AS CalendarMonthNum,

Year([Posting Date]) AS CalendarYear,

MonthName([Posting Date]) AS YearMonth,

YearName([Posting Date], 0, 4) AS FiscalYear,

Left(YearName([Posting Date], 0, 4), 4) AS ShortFiscalYear;

LOAD Date(MinDate + IterNo() -1 ) AS [Posting Date] While (MinDate + IterNo() - 1) <= Num(MaxDate);

LOAD

  Floor(MonthStart(Min([Posting Date]))) AS MinDate,

  Floor(MonthEnd(Max([Posting Date]))) AS MaxDate

RESIDENT SourceTable

ORDER BY [Posting Date];

And after that it will be link itself if you will give same name as calander date column name.

I hope this helps you

Regards,

Ashutosh