YTD Calculation in qlikview

Mon, 27 Jun 2011 06:31:19 GMT

Hi,

I need to calculate YTD of count(<measure>) in qlikview.

In my fact table, I have a date key (1, 2, 3, ...........) and in Dim_Date table there are corresponding dates for date key.

My requirement is such that, when user selects perticular date from list box of date, my chart should show YTD of count(<measure>) upto that date.

Thanks in advance.

Re: YTD Calculation in qlikview

IAMDV — Mon, 27 Jun 2011 13:54:46 GMT

Rohit - I recommend you to use the master the calendar to generate the time dimension. Please look at the attached script which covers most of the options in Time Dimension. Hope this helps!

Cheers - DV

Re: YTD Calculation in qlikview

Tue, 28 Jun 2011 06:56:45 GMT

Hi DV,

I am having personal edition of qlikview, so can't open your file .

Could you please paste the script here?

Re: YTD Calculation in qlikview

IAMDV — Tue, 28 Jun 2011 08:07:47 GMT

Calendar:

LET vDateMin = Num(MakeDate(2009,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

topic Re: YTD Calculation in qlikview in QlikView

YTD Calculation in qlikview

Re: YTD Calculation in qlikview

Re: YTD Calculation in qlikview

Re: YTD Calculation in qlikview