3 Replies Latest reply: Jun 28, 2011 4:07 AM by Deepak Vadithala RSS

    YTD Calculation in qlikview



      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
          Deepak Vadithala

          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

              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
                  Deepak Vadithala



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

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

                  LET vDateToday = Num(Today()); 




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

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

                  AUTOGENERATE 1 

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




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