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

YTD Calculation in qlikview

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.

3 Replies
Luminary
Luminary

Re: YTD Calculation in qlikview

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

Not applicable

Re: YTD Calculation in qlikview

Hi DV,

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

Could you please paste the script here?

Luminary
Luminary

Re: YTD Calculation in qlikview

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()); 

Community Browser