A rather common requirement in OLAP reporting is to show sales for a specific day, and for the previous day. Using AdventureWorks as an example, if we wanted to see sales by city for a specific date (e.g. March 1, 2004) and then show sales for the previous day (as a generic calculation), we can use the MDX PREVMEMBER statement and write the following MDX code:
WITH
MEMBER [SalesPriorDay] as
([Internet Sales Amount],
[Date].[Date].CurrentMember.PrevMember) , format_string = 'currency'
select { [Internet Sales Amount], [SalesPriorDay]} on
columns,
filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on
rows
from [adventure works]
where [Date].[Date].[March 1, 2004]
That code will produce the following result set (partial screenshot):
Recently, a student who just graduated from the SetFocus BI Master's Program emailed me and essentially asked me the following: suppose we want the column for the previous sales day to be more intelligent...i.e., suppose that for one dimension member, the 3 days prior had no sales at all, but going back 4 days, sales existed. And for another dimension member, maybe you had to go back two days to get sales, etc.
Essentially, the recent graduate was asking for a "smarter" SalesPriorDay calculation - one that would go back and find the last day where sales existed. He also asked if the result set could show the day.
To write something as generic as possible, we have to first acknowledge that the last prior day where sales existed could theoretically go back to the very first day of the database. So in plain English, we need to do the following:
- Retrieve all the days prior to the "base" date that had sales for the current member (in this case, city).
- To do this, we need to create a range from the first possible date of sales, up to the day prior to the "base" date
- From that list of days with sales, grab the last (most recent) date
Well, let's break that up into small pieces: first, to create a range of dates from the first possible date to the date prior to the "base" date, we can use the MDX Range operator (:)
{ [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}
Next, we can use the MDX filter function against that range of dates, to build a smaller set of dates where sales occured for the current member (remember that MDX is smart enough to pick up the context of the current date and each city in the row axis).
filter( {[Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0)
OK, we're close. We'd like to take the last date from the result of this filter - that would represent the last actual date where sales occured, for whatever dimension members we're using in the main query. We can use the MDX TAIL function to grab the last item in the filtered set:
tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1)
So the hard part is over, right? Not exactly. Our goal is to express the measure (Internet Sales Amount) in terms of a dimension member that represents the previous date where we have sales. But doesn't the last line fo code provide the dimension member representing the last actual date? No....remember that TAIL returns a SET, even if we provide a value of 1 for the 2nd parameter. So we have to use the ITEM() function to grab the first member of the result from the TAIL function - even if the results from TAIL only contain one tuple expression:
tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember}, [Internet Sales Amount] > 0) ,1).Item(0)
Now we can create a "smarter" calculation for Sales Prior Business Day, where that day could be several days or even weeks prior. Here's the entire solution. Note that we also show the actual date as a separate member, using the Name property.
with
MEMBER [SalesPriorBusinessDay] as
([Internet Sales Amount],
tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'
MEMBER [Measures].[LastSaleDate] AS
tail( filter( { [Date].[Date].Firstchild : [Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0).name
select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on
columns,
filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on
rows
from [adventure works]
where [Date].[Date].[March 1, 2004]
The results (below) show the sales for the date of March 1, 2004 - as well as the last actual date of sales. So notice that Ballard last had activity on February 27th, and that you have to go back an additional week to show sales for Bendigo
There is one issue with this approach - the extra logic could provide very costly, especially if we're repeatedly filtering on a large number of days. An alternate approach might be this: instead of creating a range that goes back to the first possible day of sales, let's only go back 1 week. If we don't have sales for any point in the prior 7 days, then we'll simply settle for a NULL value. In this case, we can use the MDX LAG function instead:
WITH
MEMBER [SalesPriorBusinessDay] as
([Internet Sales Amount],
tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0)) , format_string = 'currency'
MEMBER [Measures].[LastSaleDate] AS
tail( filter( { [Date].[Date].CurrentMember.Lag(7) : [Date].[Date].CurrentMember.PrevMember},
[Internet Sales Amount] > 0) ,1).Item(0).name
select { [Internet Sales Amount], [SalesPriorBusinessDay], [LastSaleDate]} on
columns,
filter( [Customer].[City].Children, [Internet Sales Amount] > 0) on
rows
from [adventure works]
where [Date].[Date].[March 1, 2004]
Using LAG will result in the following output:
So in the case of Bendigo, we consider any sales prior to the previous 7 days to be "very old". So the need for specific reporting may be need to be weighed against the consequences of slower performance.
Bottom line, the student was able to take this approach and run with it. I always feel happy when I'm able to give a graduate a hand on a query that he/she is working on, either for a job or just as part of continuing education. I certainly won't say that I'm able to answer every question from every former student - some questions are just too intricate and require that I have the data in front of me. Other cases I might be able to make some suggestions, and/or give them some websites where they might be able to get more information. But on this particular day, I got lucky. :)
At SetFocus, we often add content to our courseware, based on feedback and also based on questions/interactions. So in this instance, our MDX class will now include an example based on what I've covered here.
Humm... interesting,
Great tip!!!!
Thanks for writing, most people don't bother.
Posted by: geeks | January 21, 2010 at 07:42 AM