topic Re: Rolling 12 Month Fiscal in QlikView

Rolling 12 Month Fiscal

Wed, 16 May 2012 15:26:23 GMT

I realize numerous examples are present of this Rolling 12 Month average are already present on the community but none of them 'intergrate' with a calander(s) already defined. At least I can not find any.

Our fiscal year runs Nov to Oct. I would like the end user to select YearFiscal and the Rolling Margin chart to display the last rolling 12 month margin. The total GP for the last 12 months is just fine as well. I can then divide by 12 to find monthly average. I was able to easily do this (Rolling Margin equation) within the current fiscal year but going back in this case 5 months proved to be an issue. As the other forumns said Set Analysis is not the answer and I was unable to get 'As Of' tables to work properly. They all seemed to define thier own time parameters. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. Currently it looks like 'Current' screenshot.

Does any one of any examples of how to do this or other ideas?

Rolling Margin 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:
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

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;

Current

Goal

Re: Rolling 12 Month Fiscal

Thu, 14 Jun 2012 20:11:32 GMT

Okay I was able to perform this function however the solution is not ideal. I converted each expression into a variable. This left me with the corect answer but without a demension to graph on since this all performed in an expression.

Variables:

vRollingSumCurrent (June)

=Sum({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -12), 'MMM YY')) <=$(=Date(addmonths(Max(MonthYear),0),  'MMM YY'))"} >} Margin)/12

vRollingSumOne (May)

=Sum({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -13), 'MMM YY')) <=$(=Date(addmonths(Max(MonthYear), -1),  'MMM YY'))"} >} Margin)/12

vRollingSumTwo (Apr)

=Sum({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -14), 'MMM YY')) <=$(=Date(addmonths(Max(MonthYear), -2),  'MMM YY'))"} >} Margin)/12

Month Labels: (increase by 1 for each month back)

This will generate June

=month(Date(addmonths(Max(MonthYear), -12)))

Current Setup:

Thier has to be an easier way to caculate this using a demension.

Thanks for any help you can offer.

Re: Rolling 12 Month Fiscal

chematos — Thu, 14 Jun 2012 20:42:33 GMT

I have added the period to my calendar, so I add this:

You need to change the months.

TEMP_CALENDAR:

...

if(Month(TempFecha)=8,1,if(Month(TempFecha)=9,2,if(Month(TempFecha)=10,3,if(Month(TempFecha)=11,4,

if(Month(TempFecha)=12,5,if(Month(TempFecha)=1,6,if(Month(TempFecha)=2,7,if(Month(TempFecha)=3,8,

if(Month(TempFecha)=4,9,if(Month(TempFecha)=5,10,if(Month(TempFecha)=6,11,

if(Month(TempFecha)=7,12)))))))))))) as Periodo,

...

D_CALENDAR:

LOAD *,

If( Mes >= 11,

(Num(Periodo,'00') & '-' & Year),

(Num(Periodo,'00') & '-' & (Year-1)))

as FiscalYear

Resident TEMP_CALENDAR;

Drop table TEMP_CALENDAR;

So you have every date associated with a number of period and a date of fiscal year.

Hope this helps. I´m doing a lot of things with this calendar with fiscal years.

Re: Rolling 12 Month Fiscal

Fri, 15 Jun 2012 13:01:16 GMT

Jose thanks for the response. I have performed a situation like this already. When I click on '2012' I only go back in the current fisical year. I would like to go back 12 months. Can this be done? What equations would you use in a line chart to graph this?

Thanks.

Re: Rolling 12 Month Fiscal

chematos — Fri, 15 Jun 2012 13:42:34 GMT

Unfortunatelly I realized that I have the same problem... this is what I´m doing and I want a better way also.

This is an example of what I do to make an accumulate sum selecting one period from my field PERIOD:

sum({<[Month Year]={"$(=MonthName(Date(Addmonths(Max(%Date),-12),

'MMM-YYY')))"},PERIOD=,Year=,Month=>}[ResultadoCC])

but is not performant, I need to do 1 expression like that for each month, so I have 12 expressions to get values for a complete year....

Sorry but I can´t help you more right now.

I hope we can help us each other and find a solution

Re: Rolling 12 Month Fiscal

Fri, 15 Jun 2012 13:52:09 GMT

Ironic. This is why I started this thread. I had a tough time finding a solution with intergration into an already existing calander. My single expression solution is above as well. It is not ideal and graphing is highly limited as I am sure you discovered as well. I had a lot of views on the post but you were the first reply back with a 'working' solution.

Thanks for all your help. Perhaps someone else has a working solution.

Re: Rolling 12 Month Fiscal

nstefaniuk — Fri, 15 Jun 2012 14:30:55 GMT

Hi.

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.

Hope it makes sense

Re: Rolling 12 Month Fiscal

Fri, 15 Jun 2012 14:35:59 GMT

Do you have an example?

Re: Rolling 12 Month Fiscal

nstefaniuk — Mon, 18 Jun 2012 06:35:06 GMT

Hello,

yes I have an example. In green the methods that work, in red the on the one that doesn't work.

Re: Rolling 12 Month Fiscal

Mon, 18 Jun 2012 18:43:56 GMT

Thanks a lot nstefaniuk. This is exactly what I as looking for. I followed example 2 in the QVW. I now have a loosely coupled data because I placed the 'Period_id2/Final' in my fiscal calander as well my normal calender and these two tables were already linked together. Not sure how to resolve this at the current time but the data is correct. (We have a spreadsheet down by hand to match to.) If I figure this out I may post my example. Thanks.