En el  SolidQuality Summit 2010, preparé una sesión de MDX que fué realmente una sesión a medida de los alumnos, o al menos lo fué en la medida de lo posible. En esa sesion, una de las preguntas iba en el hilo de calcular no solo el acumulado anual, sino también el acumulado anual de años anteriores. 

En las siguientes lineas iremos viendo como hacer esto en MDX. La primera parte será crear el acumulado anual, para eso, nos valdremos de una dimensión tiempo bien definida, en este mismo portal tenéis ejemplos de como hacerlo y podéis ver que se entiende por bien definida, así que no me entretendré mucho en esa explicación.  Es necesario que esté definida bien como tiempo nuestra dimensión, básicamente porque  vamos a usar las funciones YTD() y paralellperiod, ambas necesitan esa definición de forma adecuada. YTD() devuelve un conjunto , que representan en el nivel actual todos los periodos desde el pricipio de año. Paralleperiod, recibe 2 o 3 parámetros, el nivel, el número de elementos y el miembro, en mis ejemplos vereis suministrados 2, el tercero, el miembro será el currentmember de la dimensión tiempo bien definida.

Comenzamos con el acumulado anual, aquí teneis el código para Adventure Works

with member acumuladoanual as
 sum(ytd(),Measures.[Internet Sales Amount])
select acumuladoanual on 0, 
[Date].[Calendar].[Month] on 1
from [Adventure Works]

ytd() es un conjunto, de los meses en este caso, y el sum(ytd(), ventas), lo que hará será sumar todos los valores de la tupla mes,Venta, es decir, mostrará el acumulado del año que estemos visualizando, que es en resumen lo que queríamos conseguir.
De no usar YTD(), podríamos usar funciones equivalentes recorriendo jerarquías, ya sean tiempo o no, y usando rangos… dejamos eso para el lector o para futuras ocasiones.

Lo siguiente que necesitamos es el periodo anterior, es decir, el valor de las ventas para el mismo mes del año anterior. La función que vamos a usar es ParallelPeriod, de la que ya hemos comentado los parámetros. La fórmula quedaría como sigue:

with member anioanterior as
    (parallelperiod([Date].[Calendar].[Calendar Year],1),
     Measures.[Internet Sales Amount])
select {Measures.[Internet Sales Amount],anioanterior} on 0,
[Date].[Calendar].[Month] on 1
from [Adventure Works]

El resultado, el esperado, obtendremos el valor de las ventas del mismo mes del año anterior. Esta función es curiosa cuando se trata de otros paralelismos, por ejemplo a nivel de día ¿que pasa con los 29s de febrero de los años bisiestos? La función esta implementada realmente como un Cousin, es decir como el primo al mismo nivel que yo por lo que puede ser que no exista y ponga nulo o que sea otro miembro (como pasa si comienzas tu dimensión tiempo en Julio al estilo de Adventure Works) obteniendo datos que pueden no tener sentido de negocio. Sed cuidadosos con esto y sobre todo comprobadlo .

Y ahora ¿lo usamos todo junto?

with member acumuladoanual as
 sum(ytd(),Measures.[Internet Sales Amount])
 member anioanterior as
    (parallelperiod([Date].[Calendar].[Calendar Year],1),
     Measures.[Internet Sales Amount])
member anioanterioracum as
    (parallelperiod([Date].[Calendar].[Calendar Year],1),
     Measures.[acumuladoanual])     
select {Measures.[internet Sales Amount],anioanterior,acumuladoanual,anioanterioracum} on 0, 
[Date].[Calendar].[Month] on 1
from [Adventure Works]

En este caso estaremos obteniendo por un lado, el importe de ventas, las ventas del mismo periodo del año anterior, el acumulado de este año, y usando ambas combinadas el valor de la misma venta el año anterior

Espero que se entienda y os guste.

Haca unos dias, un amigo, y alumno de un curso de MDX que impartí lanzaba esa pregunta en los post, ¿como saber los artículos que no han tenido venta en los últimos tres meses. Para eso hace falta saber que dia es hoy, aunque en realidad, me parece que es mejor olvidarse de que dia es hoy y hacerlo genérico. Vamos a ver como podriamos hacer eso.

Bueno, vamos a ir por partes.. lo primero que vamos a hacer es ver como calcular el día de hoy.

with member hoy
as
 now()
select hoy on 0
from [Adventure Works]

despues vamos a ver como sacar el conjunto de articulos con ventas en un mes.

select  exists([Product].[Product].product*[Date].[Calendar].[Month].members,,
		  "Internet Sales")
  on 1,
        {Measures.[Internet Sales Amount]} on 0
from  [Adventure Works];

el conjunto que forman los meses y  los productos que han tenido ventas es lo que nos devuelve la clasula de arriba.

Ahora vamos a calcular los que no han tenido ventas en absoluto, para ello simplemente hacemos una resta de conjuntos, es decir, todos los productos menos los que han tenido ventas

select  [Product].[Product].product*[Date].[Calendar].[Month].members-
exists([Product].[Product].product*[Date].[Calendar].[Month].members,,
		  "Internet Sales")
  on 1,
        {Measures.[Internet Sales Amount]} on 0
from  [Adventure Works];

De esta forma tenemos los articulos sin ventas en un mes concreto, porque son aquellos que no tienen datos en el grupo de medidas Internet Sales, para el mes en cuestion. El problema viene porque ahora necesitamos saber los que no tienen ventas en los últimos tres meses. Para poder usar Exist, necesitariamos tener un grupo especial que tuviese en el mes actual las ventas de los últimos tres meses para cada producto y comprobar su existencia. El resultado sería rápido, pero parece que quedaría bastante engorroso de hacer, además implica cambios en el ETL y eso siempre quiere decir, mucho tiempo, muchas pruebas. Así pues dejamos esta via solamente para el caso de que el rendimiento no se corresponda con el necesario. En otro caso pasamos al plan B y usamos el método filter.

	select
	filter ( [Product].[Product].product*
				[Date].[Calendar].[Month].members  ,
				 sum(([Date].[Calendar].currentmember.lag(3):[Date].[Calendar].currentmember),Measures.[Internet Sales Amount])>0) on 1,
				Measures.[Internet Sales Amount] on 0
	from  [Adventure Works];

si nos fijamos he usado la cláusula Sum para el filter, esto penalizará rendimiento, sin duda,.. considerad hacer esta medida física aun si es en el mismo grupo de medidas para mejorar el rendimiento. Aún este ejemplo nos está mostrando los que si tienen ventas en los últimos 3 meses.. veamos los que no… creo que se vé facil, si has llegado hasta aquí

	select [Product].[Product].product*[Date].[Calendar].[Month].members -
	filter ( [Product].[Product].product*
				[Date].[Calendar].[Month].members  ,
				 sum(([Date].[Calendar].currentmember.lag(3):[Date].[Calendar].currentmember),Measures.[Internet Sales Amount])>0) on 1,
				Measures.[Internet Sales Amount] on 0
	from  [Adventure Works]

Esta consulta tardó casi 15 segundos (una barvaridad) en mi portatil contra adventure works, si la personalizamos a un único mes.. tardará mucho menos, claro.

	select [Product].[Product].product-
	filter ( [Product].[Product].product
				,
				 sum(([Date].[Calendar].currentmember.lag(3):[Date].[Calendar].currentmember),Measures.[Internet Sales Amount])>0) on 1,
				Measures.[Internet Sales Amount] on 0
	from  [Adventure Works]
	 where
	[Date].[Calendar].[Month].&[2004]&[7]

y si ahora lo enlazamos con el now que veiamos al principio.. (ojo , no funcionará a menos que le pongas el reloj al server a un valor que de un miembro válido) Además ojo con usar STRTOMEMBER, que puede dar también problemas de rendimiento, no en este ejemplo.. pero si en general

with member ahora as
    "[Date].[Calendar].[Month].&[" +cstr(year(now()))+ "]&["+cstr(month(now()))+"]"
	select [Product].[Product].product-
	filter ( [Product].[Product].product
				,
				 sum(([Date].[Calendar].currentmember.lag(3):[Date].[Calendar].currentmember),Measures.[Internet Sales Amount])>0) on 1,
				Measures.[Internet Sales Amount] on 0
	from  [Adventure Works]
	 where
	strtomember(ahora)