Make a R12 (Rolling 12) over the entire data model

In this post I will describe one way to generate reports of the type R12 (Rolling 12), ie the last 12 months or better still last 13 in order to compare the ends.

If we perform a Google search on how to generate a QV R12 will find many questions and examples based on the use of the date type functions “MonthStart”, “Monthend” and others, but in general what is explained is how to generate one (just one) R12 graphic. But here I will show how to generate a R12 CALENDAR (or Rxx) which means that when the user selects a given date, internally QV selects all rows for the last 12 months on ALL the data model, not just for a chart or a particular table.

For enable a full understanding the user story (requirement) could be something like: “… I want to select one (and only one) period (month-year) and all the board’s graphics must show back last 13 months and I don’t want to have to select (paint) 13 months manually “.

Because a board of this type will have a calendar that segments the rest of the data model what we will do shall be leverage that calendar to achieve the desired effect.

In a typical calendar when a user clicks on a month and year QV selects the possible values ​​that correspond to that period. What we want is that when the user selects a period QV selects the 12 possible values ​​of previous periods.

To achieve this we will create a  “intermediate” calendar where each period is repeated 12 times to contain the previous 12 months. Eg: intermediateTable

And we will “insert” into the model as follows:

insert_in_Model2 

Thus, when the user selects R12_Period = “Dic/2013” possible values ​​will be selected as the 12 (or 13) months prior to the Period field which linked with the calendar and this one with the rest of the model.

Below the display board and the script::

dashboard
Sales:
Load * inline [
Period, Sales
01/01/2011, 4000
01/02/2011, 3500
01/03/2011, 6000
01/04/2011, 6000
01/05/2011, 6500
01/06/2011, 4000
01/07/2011, 4500
01/08/2011, 5500
01/09/2011, 5000
01/10/2011, 8000
01/11/2011, 8500
01/12/2011, 9000
01/01/2012, 10000
01/02/2012, 10500
01/03/2012, 8000
01/04/2012, 7500
01/05/2012, 9000
01/06/2012, 9200
01/07/2012, 10000
01/08/2012, 12000
];

Comissions:
Load * inline [
Period, Wages
01/01/2011, 2000
01/02/2011, 2500
01/03/2011, 4000
01/04/2011, 1500
01/05/2011, 500
01/06/2011, 3000
01/07/2011, 3500
01/08/2011, 3500
01/09/2011, 2000
01/10/2011, 8000
01/11/2011, 8000
01/12/2011, 8000
01/01/2012, 11000
01/02/2012, 5000
01/03/2012, 7000
01/04/2012, 7000
01/05/2012, 7000
01/06/2012, 9000
01/07/2012, 9000
01/08/2012, 11000
];

MAX_MIN:
LOAD
 Min(Period) as First,
 Max(Period) as Last
Resident Sales;

Let V_StartPeriod = Peek(‘First’,0);
Let V_EndPeriod   = Peek(‘Last’,0);

Drop Table MAX_MIN;

Let V_Period_to_Select = num(V_StartPeriod);

DO WHILE V_Period_to_Select <= V_EndPeriod //generate all data’s periods

     Calendar_to_Select:
     LOAD
     $(V_Period_to_Select) as R12_Period
     AUTOGENERATE 1;
    
     LET V_Selected_Period = num(V_Period_to_Select);
// Generate 13 previous month for each one
     DO WHILE V_Selected_Period >= AddMonths(V_Period_to_Select,-12)
    
           Calendar_to_Link:
           LOAD
           $(V_Period_to_Select) as R12_Period,
           $(V_Selected_Period) as Period
           AUTOGENERATE 1;
           LET V_Selected_Period =  num(AddMonths(  V_Selected_Period, -1 ));  
          
     LOOP
    
    
     LET V_Period_to_Select =  num(AddMonths(  V_Period_to_Select, 1 ));
    
LOOP

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: