Tag Archives: Aggregate

Comparable day in MDX using “lag” or “ParallelPeriod”

Posted on by • Posted in MDX • Tagged , , , , • Leave a comment

In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

Comparable day 1

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Comparable day 2

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.

Aggregate using “summarize”

Posted on by • Posted in Basic script • Tagged , , • Leave a comment

In DAX we have a function called “summarize”. This function aggregates totals over a set of groups.

Here is an example on how this function can be used:

 
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
    "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
)

The code above will give you sales and quantity per month per year. The first argument is the table name. The second and third argument is the columns we want to order by. Fourth and fifth argument is the numbers you want to aggregate.

If you want to use some of the predefined measures you can skip the sum and write like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)

If you manually want to set the order by clause this can be set like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)
ORDER BY 'Date'[Month Name], 'Date'[Calendar Year]

The MDX that will give you the same result can look like this:

SELECT NON EMPTY [Date].[Calendar Year].[Calendar Year] * [Date].[Month Name].[Month Name] ON 1,
{ [Measures].[Internet Total Sales], [Measures].[Internet Total Units] } ON 0
FROM [Internet Operation]

And the SQL to get the similar resulat might look like this:

SELECT dimdate.calendaryear, 
       dimdate.englishmonthname, 
       Sum([salesamount])   AS 'Internet Total Sales', 
       Sum([orderquantity]) AS 'Internet Total Units' 
FROM   [factinternetsales] 
       INNER JOIN dimdate 
               ON dimdate.datekey = [factinternetsales].[orderdatekey] 
GROUP  BY dimdate.calendaryear, 
          dimdate.monthnumberofyear, 
          dimdate.englishmonthname

The result will be like this:

Summarize