MTD is not supported by Qlikview. So you should create a master calendar as below:
Then you should change the dimension of your pivot table as below:
IF(cal_mtd_current=1,Month(date))
and
IF(cal_mtd_previous=1,Month(date))
Range: LOAD min(Datefield) as startdate, max(Datefield) as enddate resident Sales;
//Peek out the values for later use let vStart = peek('startdate',-1,'Range')-1; let vEnd = peek('enddate',-1,'Range'); let vRange = $(vEnd) - $(vStart); //Remove Range table as no longer needed Drop table Range; //Generate a table with a row per date between the range above Date: Load $(vStart)+recno() as Date autogenerate $(vRange); //Calculate the Parts you need to examine Calendar: load //Date as MyDate, date(Date,'DD.MM.YYYY') as Cal_FullDate, date(Date,'DD.MM.YYYY') as Datefield, Year(Date) as Cal_Year, 'Q'&ceil(Month(Date)/3) AS Cal_Quarter, Month(Date)&'-'&right(year(Date),4) as Cal_MonthYear, Month(Date) as Cal_Month, Day(Date) as Cal_Day, Week(Date) as Cal_Week, Weekday(Date) as Cal_WeekDay, YearToDate(Date , 0, 1, $(v_date))*-1 AS Cal_YTD_Current, YearToDate(Date , -1, 1, $(v_date))*-1 AS Cal_YTD_Previous, YearToDate(Date , 0, Month('$(v_date)'))*-1 AS Cal_MTD_Current, YearToDate(Date , -1, Month('$(v_date)'))*-1 AS Cal_MTD_Previous
