Find first order date in MDX

September 3, 2012

I had an interesting question the other day; “how do I find the date of the first order/activity for a given customer/employee/product etc in MDX”?

Well you’ll be pleased to know that it’s pretty simple. The sample code bellow will work with the AdventureWorks DW 2008R2 cube.

First of all we need to filter the date dimension to contain only those with orders. Then take the first item of the resulting set, then find its name. Simples.

Filter the date hierarchy to only those dates with a Reseller Order Count:

   FILTER([Date].[Date].[Date].MEMBERS, [Measures].[Reseller Order count])

Note that you can specify a more advanced filter, such as [Measures].[Reseller Order Count]>10, which would find the first date which had more than 10 orders.

Then find the first item:

   .ITEM(0)

Then find the its name:

   .NAME

Put this together in a query and you get:

   WITH MEMBER [Measures].[First Activity] AS
       FILTER([Date].[Date].[Date].MEMBERS
            , [Measures].[Reseller Order count]).ITEM(0).NAME
   SELECT {[Measures].[Reseller Order count]
         , [Measures].[First Activity]
       } ON 0,
      [Employee].[Employees].MEMBERS ON 1
   FROM [Adventure Works]

This returns the total reseller order count for each employee, along with the date of their first reseller order. Note that the Employee hierarchy here is a parent child hierarchy. The calculated member [First Activity] will aggregate and calculate correctly with any dimension or member specified on the 1 (ROWS) axis, be it a parent-child, single attribute, normal hierarchy, etc. and will always find the first order date for the current member.
You should get the following results:

Frog-Blog Out.

Posted in Analysis Services |

Tags: Analysis Services, Calculated Member, MDX, OLAP, SSAS

5 comments on “Find first order date in MDX”

Bill Dumler on January 9, 2013 at 14:30 said:

Hoping you could help me with a calculations I am trying to define, it seems somewhat similar.

Trying to define a calculation which will provide a last years $ sold for weeks select by the user but there are two calendars they can select from. The calculation gives $ sold for weeks select. The following works with one calendar.

SUM(Existing [CalendarRetail].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarRetail].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]))

Here are the results if I filter on last 4 most recent weeks. The values are the $ sold last year for the same week.

Date Sales $ LY
11/24/2012 $16,464,551
12/01/2012 $18,666,707
12/08/2012 $23,775,142
12/15/2012 $28,025,152

The calculations should return $ sold for each week, not a sum over the weeks. Is it possible to write the calculation to look at two calendars – CalendarRetail and CalendarFiscal (another calendar accessible by the user).

Thanks
Bill

Reply ↓

Alex on January 11, 2013 at 16:36 said:

Hi Bill

There are a couple of approaches to this, depending on whether it’s in a query or can be done in the MDX calculations script.

A simple solution is to check ParallelPeriod on both date hierarchies and then decide which one to use by checking the currentmember of one of the date hierarchies. i.e. If the currentmember of one hierarchy is ALL then use the other hierarchy. This is a technically poor solution in terms of performance, we should avoid IIF statements where possible, but it gives us a starting point.

This code does what you want on the Adventure Works DW 2008R2 cube.

WITH MEMBER [Measures].[Sales LY] AS
    IIF([DATE].[Fiscal Weeks].CurrentMember IS [DATE].[Fiscal Weeks].[ALL]
        , (PARALLELPERIOD([DATE].[Calendar Weeks].[Calendar YEAR],1), [Measures].[Internet Sales Amount])
        , (PARALLELPERIOD([DATE].[Fiscal Weeks].[Fiscal YEAR],1), [Measures].[Internet Sales Amount]))
SELECT
    {[Measures].[Internet Sales Amount]
     ,[Measures].[Sales LY]
    } ON 0,
    DESCENDANTS([DATE].[Calendar Weeks]) ON 1
    --DESCENDANTS([Date].[Fiscal Weeks]) ON 1
FROM [Adventure Works]

For a better solution, the switching between different date hierarchies should be done using SCOPE in the MDX calculation script instead of IIF. Have a look at http://www.PurpleFrogSystems.com/blog/2009/11/scope-problems-with-mdx-calculated-members/ to give you a pointer on how to do this.

Alex

Reply ↓

Bill Dumler on January 11, 2013 at 17:18 said:

Alex

Thanks. Implement the measure using Scope and it works perfectly!

CREATE MEMBER CURRENTCUBE.[Measures].[ Sales $ LY]
AS “”,FORMAT_STRING = “$#,##;($#,##)”, VISIBLE = 1;
Scope([CalendarRetail].[Year-Period-Week].[Date].MEMBERS);
[Measures].[ Sales $ LY]=Sum(Existing [CalendarRetail].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarRetail].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]));
End Scope;
Scope([CalendarFiscal].[Year-Period-Week].[Date].MEMBERS);
[Measures].[ Sales $ LY]=Sum(Existing [CalendarFiscal].[Year-Period-Week].[Date].MEMBERS, (ParallelPeriod([CalendarFiscal].[Year-Period-Week].[Year Number]), [Measures].[Dollars Sold]));
End Scope;

Thanks
Bill

Reply ↓

shubham on April 16, 2013 at 06:06 said:

If i want to get last order date then how it will be possible

Reply ↓

Alex on April 16, 2013 at 18:21 said:

Using ITEM(0) gets you the first member of a set. You can get the last member by using TAIL().

Technically TAIL() returns a set, so you then need to return the first member of that set, using ITEM(0) as before.

TAIL(FILTER([Date].[Date].[Date].MEMBERS, [Measures].[Reseller Order count])).ITEM(0).NAME

Hope this helps
Alex

Reply ↓

Purple Frog Systems

Find first order date in MDX

5 comments on “Find first order date in MDX”

Leave a Reply Cancel reply