Qlikview Rolling 12 Month Fiscal

Question

This forum seems to be more geared toward more programming issues but I would be interested if someone would look at the logic issue in the below link. My issue is how to calculate the previous 12 month total using the calendar already defined. I have found very few examples of this on the QV community. Please see the below link for more details. I would be willing to look at a SQL or a QV script solution.

Our fiscal year runs Nov to Oct. I would like the end user to select Year and a chart to display the last rolling 12 month margin. I have had issues getting my total to accumulate for previous months. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. (look at QV link for screenshot)

Rolling Margin equation: my attempt to use Set Analysis to make a rolling avg equation

=Sum({<master_date={'>=$(=MonthStart(Max(master_date), -12))<=$(=MonthEnd(Max(master_date)))'}>}

MasterCalendar

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar: this uses master_date to connect items together. This an fiscal calendar are hard to put together with rolling avg

LOAD
TempDate AS master_date,
Day(TempDate) AS CalendarDay,
      WeekDay(TempDate) AS CalendarWeekDay,
      Week(TempDate) AS CalendarWeek,
      Month(TempDate) AS CalendarMonth,
      Year(TempDate) AS CalendarYear,
      'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
      WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
      Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
      If(Num(TempDate) >= $(vYearStart) AND Num(TempDate) < $(vMonthNow), -1, 0) AS YTD, 
      If(Num(TempDate) >= $(vYearStartLY) AND Num(TempDate) < $(vMonthNowLY), -1, 0) AS LY_YTD,
      Year2Date(TempDate) * -1 AS YTD_Flag,
      Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LY_YTD_Flag
RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

FiscalCalander> This defines our fiscal year

FiscalCalendar:
LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
AUTOGENERATE today()-date#(20011101,'YYYYMMDD');
LEFT JOIN (FiscalCalendar)
LOAD
"master_date",
date(monthstart(master_date),'MMM YY') AS "MonthFisical",
date(monthstart(master_date),'MMM') AS "MonthFisical_MonthTitle",
date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
month(master_date)-month(num(today(1))) AS FiscalMonthsElapsed,
YearToDate(master_date, 0,11)*-1 AS YTD_FLAG_Fiscal,
YearToDate(master_date,-1,11)*-1 AS LY_YTD_FLAG_Fiscal
RESIDENT FiscalCalendar;

To see screen shots: http://community.qlikview.com/message/219912#219912

Thank you for taking the time to look at this issue.

Answer 1

The solution is not in the calendar : you have to create a pivot table between your calendar and the fact table.

In this pivot table you have 2 type : DIRECT and CROSSING.

For type DIRECT, a row in fact table is linked to the date in calendar For type CROSSING, a row in fact table is linked to all the dates of the 12 future months in calendar.

So in Qlikview, you use the type DIRECT all the time, except when you want to present for each month the total of the past 12 months. In this case you use CROSSING because all rows are linked to the dates of the 12 future months, so it means (reversed point of view) that a month is linked to all data of the past 12 months.

Examples:

xlsx

QVW