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.
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
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 https://purplefrogsystems.com/2009/11/scope-problems-with-mdx-calculated-members/ to give you a pointer on how to do this.
Alex
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
If i want to get last order date then how it will be possible
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
Hi
Tail() command helped very much in my previous project. Thank you Alex.
My new project is also to find the latest Startdate of an employee but I have to display the corresponding enddate (or NULL if the employee is still working). along with department name linked to the latest date.
WITH
MEMBER [Measures].[LatestStartDate] AS
sum(
tail(
nonempty([Date].[Full Date].CurrentMember,[Measures].[No_of_Emp])
,1)
,[Measures].[No_of_Clients]
)
MEMBER [Measures].[LatestEmpStartDATE] AS
TAIL(FILTER([Emp_StartDate].[Full Date].MEMBERS
, [Measures].[LatestStartDate])).ITEM(0).NAME
MEMBER [Measures].[LatestEmpEndDATE] AS
TAIL(FILTER([Emp_EndDate].[Full Date].MEMBERS
, [Measures].[LatestStartDate])).ITEM(0).NAME
SELECT
{
MEASURES.[LatestStartDate]
, [Measures].[LatestEmpStartDATE]
, [Measures].[LatestEmpEndDATE]
} on columns
,NON EMPTY
[Employee].[EmpID].members
on ROWS
Form this code I am getting LatestEmpEndDATE but not linked to the corresponding LatestEmpStartDATE.
Thanks in advance
There’s nothing in your code that restricts the end date to only that corresponding to the equivalent start date.
So to fix it you need to restrict the set of end dates to only those after the latest start date.
You can do that using the LinkMember function; find the last start date, then map that date to the end date dimension, then only use dates after that to find the last end date.
Something like this
MEMBER [Measures].[LatestEmpStartDate] AS
TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
MEMBER [Measures].[LatestEmpEndDate] AS
TAIL(NONEMPTY(
{LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
,[Emp_EndDate].[Full Date]) : null}
, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
Hi Alex
Thank you very much. This code worked for me..
Thanks again
Dear Alex
If I use the same technique to bring in the Manager ID of the latest Start Date, I am getting Null values. Please advise, whehter I am missing some thing here
Thank you once again in advance
WITH
MEMBER [Measures].[LatestEmpStartDate] AS
TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
MEMBER [Measures].[LatestEmpEndDate] AS
TAIL(NONEMPTY(
{LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
,[Emp_EndDate].[Full Date]) : null}
, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
MEMBER [Measures].[LatestMGR] AS
TAIL(NONEMPTY(
{LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
, [DIM_Manager].[Mgr_Code])}
, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
SELECT
{
[Measures].[LatestEmpStartDate]
, [Measures].[LatestEmpEndDate]
, [Measures].[LatestMGR]
} on columns
,NON EMPTY
[Employee].[EmpID].members
on ROWS
From Cub_
Dear Alex
If I use the same technique to bring in the Manager ID of the latest Start Date, I am getting Null values. Please advise, whehter I am missing some thing here
Thank you once again in advance
WITH
MEMBER [Measures].[LatestEmpStartDate] AS
TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
MEMBER [Measures].[LatestEmpEndDate] AS
TAIL(NONEMPTY(
{LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
,[Emp_EndDate].[Full Date]) : null}
, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
MEMBER [Measures].[LatestMGR] AS
TAIL(NONEMPTY(
{LINKMEMBER(TAIL(NONEMPTY([Emp_StartDate].[Full Date].[Full Date].MEMBERS, [Measures].[No_of_Emp]),1).ITEM(0)
, [DIM_Manager].[Mgr_Code])}
, [Measures].[No_of_Emp]),1).ITEM(0).MEMBER_NAME
SELECT
{
[Measures].[LatestEmpStartDate]
, [Measures].[LatestEmpEndDate]
, [Measures].[LatestMGR]
} on columns
,NON EMPTY
[Employee].[EmpID].members
on ROWS
From Cub_
This worked for me. The first few results I checked suggested using the TOPCOUNT function for this situation, but I couldn’t get that working. Thanks.
Thanks for the article. Can you please expand the query to total the number of First Activities per Month / Day. This can also represent the number of new users / activities?
Thanks a lot!
J
Hi John
Yes sure – just add in another calculated measure…
MEMBER [Measures].[Activities In First Month] AS
([Measures].[Reseller Order count]
, FILTER([Date].[Date].[Date].MEMBERS
, [Measures].[Reseller Order count]).ITEM(0)
)
This calculates the order count measure, but filtered to only the first member of the date hierarchy that contains a value.
Alex
Hi Alex,
Thanks for this article. It helps a lot!
Following your example, I was able to get the first payment date and the first sale date. However, it threw error “Type Mismatch” when i tried to subtract those 2 dates, FirstPmtDate – FirstSaleDate. Any suggestion?
Thanks,
Jane
Hi Jane
Sorry for delay in reply. You can’t just subtract dates in MDX, but you can use Datediff to achieve this.
Chris has a good example here: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/
Regards
Alex
I have a scenario where a product is introduced on ‘1/1/2014’ and another product on ’10/1/2014” what to find out the first 30 day sales of both and compare
This is a data modelling problem, not an MDX problem. You need to add a ‘Product Age’ dimension, representing the number of days between the launch of a product and the date of each sale.
in general how to calculate first 30 days sum,60 days sum,90 days sum,120 days sum in cubes.
To sum up a range you can just use SUM({set}, measure)
So SUM({[Date].[Date].Lag(29):[Date].[Date].currentmember}, [Measures].[Sales])
Would sum up over the last 30 days.
The problem is that you don’t want the last 30 days, you want the first 30 days from the date a product launched. And that date changes from product to product. So how do you define the first day from which to start your set from. And that’s why you should look to do this with an extra dimension and not using complex MDX.
Got it. I have week of year total. Can i further drill down so that i can do (week of year total)/Number of Days in that week ?
You could have a new measure group, with the granularity of date, and a value of 1 in each row. This is the fastest option, but relies on adding a new measure group to the cube.
Alternatively you could use something like:
COUNT(Descendants([Date].[Calendar].CurrentMember,,LEAVES),INCLUDEEMPTY)
Which will could the number of leaf members of the Calendar hierarchy in the Date dimension.
in general how to calculate first 30 days sum,60 days sum,90 days sum,120 days sum in cubes
I’m new to cubes currently filling the gap of my teammate who is on vacation.. which books should i go through to get a fair understanding of SSAS, mdx queries
My favourite MDX book has always been “Fast Track to MDX” by Whitehorn, Zare & Pasumansky.
It’s out of print now so can be expensive and difficult to get hold of.
Alternatively you can’t go far wrong with anything by Chris Webb, Alberto Ferrari or Marco Russo
Thanks!!
Hi Alex,
I need some help. I am trying calculated the Exponential Moving Average, and i need get the first 9 dates in the AVG, and after 9 using the formula. I do everything using the formula to EMA, but i cant get the first 9 dates.
ill be appreciate for your help.
Hi Tulio
To get the first 9 dates you can just use the MDX Head function:
Head({Set}, 9)
In this case, the set would be the members of the date dimension, so:
Head({[Date].[Date].[Date].Members}, 9)
Or you may need to add a nonempty around it, to make sure you don’t include blank dates
Head(NONEMPTY([Date].[Date].[Date].Members, [Measures].[xxx]), 9)
Hi Alex,
thanks a lot for this article. I have tried to apply this approach on my case, but it does not work yet.
There is a measure [Capacity] with MAX-aggregation. We need to see the corresponding leaf-Element in DATE.Dimension. The date, on wich the maximum occured.
The filter below works only with fixed-Elements within the tuple. Changing to CurrentMember causes incorrect results.
Are there any restrictions known to the filter()-function?
Thanks a lot in advance for any hint.
Matze
CREATE MEMBER CURRENTCUBE [Measures].[DateOfMaxCapacity]
AS Filter (
[DATE].[Calender].[DATE].Members,
[Measures].[MaxCapacity]= (
[DATE].[Calender].currentMember,
[DATPKT].[Function].currentMember,
[DATPKT].[Medium].currentMember,
[Measures].[MaxCapacity]
)
).Item(0).Name
Hi Matze
The easiest way to achieve this is to use the example in the script in the post, but sort the set before the filter is applied. Someting like:
MEMBER [Measures].[Max Activity] AS
FILTER(
ORDER([Date].[Date].[Date].MEMBERS ,
[Measures].[Reseller Order count], BDESC),
[Measures].[Reseller Order count]).ITEM(0).NAME
Regards
Alex
Hi Alex! Excellent post!
I need to implement this exact calculation. I tried to do it with “item(0). name” but got “The type of the child node is invalid”, so I tried “item(0).item(0).name” and passed without error. But instead of getting the actual date, I get a number 1 in the measure.
Your help will be deeply valued!
My code is :
WITH
MEMBER [Measures].[First Activity] AS
FILTER([0NETDUEDATE].[LEVEL01].MEMBERS
,NOT ISEMPTY([Measures].[Saldo])).ITEM(0).ITEM(0).NAME
SELECT
{[Measures].[Saldo]
, [Measures].[First Activity]
}
ON 0,
NON EMPTY
{[Cliente].[0000999673],[Cliente].[0000206496]}
ON 1
FROM [Cube]
RESULT
Cliente Saldo First_Activity
999673 5000 1
206496 4000 1
Hi Alex! Excellent post!
I need to implement this exact calculation. I tried to do it with item(0). name, but got The type of the child node is invalid, so I tried item(0).item(0).name and passed without error. But instead of getting the actual date, I get a number 1 in the measure.
Your help will be deeply valued!
My code is :
WITH
MEMBER [Measures].[First Activity] AS
FILTER([0NETDUEDATE].[LEVEL01].MEMBERS
,NOT ISEMPTY([Measures].[Saldo])).ITEM(0).ITEM(0).NAME
SELECT
{[Measures].[Saldo]
, [Measures].[First Activity]
}
ON 0,
NON EMPTY
{[Cliente].[0000999673],[Cliente].[0000206496]}
ON 1
FROM [Cube]
RESULT
Cliente Saldo First_Activity
999673 5000 1
206496 4000 1