Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 208

In this query, we cross join the Product and Order Date dimensions to display
the product and the month on the row axis. The calculated measure TotalDisc
multiplies the discount, quantity, and unit price measures to compute the
total discount amount of the current product and month.

Sales Amount TotalDisc

Alice Mutton July 1996 $936.00 $0.00

Alice Mutton August 1996 $819.00 $117.00

Alice Mutton September 1996 $1,248.00 $0.00

Alice Mutton October 1996 $2,948.40 $50.96

··· ··· ··· ···

Query 6.10. Monthly year-to-date sales for each product category.
WITH MEMBER Measures.YTDSales AS
SUM(PERIODSTODATE([Order Date].Calendar.[Year],
[Order Date].Calendar.CURRENTMEMBER),
Measures.[Sales Amount]), FORMAT STRING = ' ###,##0.00 '
SELECT DESCENDANTS([Order Date].[1996], [Order Date].[Month])
ON COLUMNS, Product.[Category].MEMBERS ON ROWS
FROM Sales
WHERE (Measures.YTDSales)
Here, we use the PERIODSTODATE function in order to select all months of
the current year up to the current month. Then, the SUM function is applied
to obtain the year-to-date aggregate value of the measure Sales Amount .

July 1996 August 1996 September 1996 October 1996 ···

Beverages $3,182.50 $6,577.38 $8,996.98 $15,700.82 ···

Condiments $1,753.40 $3,141.70 $4,003.30 $8,127.62 ···

Confections $5,775.15 $10,781.92 $16,527.92 $20,056.52 ···

Dairy Products $6,838.34 $11,600.04 $14,416.04 $21,353.59 ···

Grains/Cereals $1,158.86 $1,429.46 $2,159.06 $4,530.02 ···

Meat/Poultry $2,268.72 $5,764.38 $10,055.38 $13,706.68 ···

Produce $3,868.80 $4,673.12 $5,837.92 $6,700.92 ···

Seafood $2,400.33 $6,383.07 $8,936.87 $14,748.87 ···

Query 6.11. Moving average over the last 3 months of the sales amount by
product category.

WITH MEMBER Measures.MovAvg3Months AS
AVG([Order Date].Calendar.CURRENTMEMBER.LAG(2):
[Order Date].Calendar.CURRENTMEMBER,
Measures.[Sales Amount]), FORMAT STRING = ' $###,##0.00 '
SELECT [Order Date].Calendar.Month.MEMBERS ON COLUMNS,
Product.[Category].MEMBERS ON ROWS
FROM Sales
WHERE (Measures.MovAvg3Months)