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 link below.

My issue is about how to calculate the previous 12 months' total using the calendar already defined. I have found very few examples of this on the QV community. Please see the link below for more details. I would be willing to look at an SQL or a QV script solution.

Our fiscal year runs Nov to Oct. I would like to have the end user select the Year and a chart to display the last rolling 12 months' 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 calculated 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;

FiscalCalendar: 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 screenshots: http://community.qlikview.com/message/219912#219912

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

Caustic

I hope this contains enough information/context – Caustic

Seems that community.qlikview.com/message/219912#229267 contains a solution. – Walton

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

Hysell