topic YTD,MTD,QTD calculation in scripts. in QlikView

YTD,MTD,QTD calculation in scripts.

Tue, 26 Nov 2013 07:28:28 GMT

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,

Re: YTD,MTD,QTD calculation in scripts.

Tue, 26 Nov 2013 07:53:38 GMT

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