Very often you’ll come across a requirement for users to be able to aggregate values across abitrary date ranges. For example, they might want to see the sum of Unit Sales between any two months, say Feburary and April 1997. While this is fairly easy to accomplish with a calculated member when you have control of the MDX, when your users want to use an off-the-shelf client it becomes more difficult – how do you allow people to pick a start date and an end date on your single time dimension?
The way I usually solve this is by creating two time dimensions, one for the start date and one for the end date. However if you hang both off the time key column in your fact table you’ll see that initially no data is returned unless you select the same members on both dimensions – a transaction can’t be in two months simultaneously. You can get round this in a calculated member and sum up the values between the two months, but its not very elegant. What you really want is for your start date dimension to act like a normal dimension and for the end date dimension to be present in the cube but have no effect on it, so then in your calculated member you can simply aggregate all the values between the currentmember on start date and the member on start date which equates to the currentmember on end date.
This is actually very easy to do in AS2005 as you can add a dimension to a measure group without it having any relationship to the fact table. In AS2K though you need to go through the following steps:
- Create your cube with one time dimension as usual, and call it Start Date
- Copy this dimension and paste it to the same database, and when asked to rename it call it End Date
- Create a virtual cube containing everything from your original cube.
- To add the End Date dimension to this virtual cube you can’t, unfortunately, use Analysis Manager, you have to use some DSO code. Here’s a bit of VBScript as an example:
Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.server")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update
- Now you have a virtual cube with two time dimensions, but the End Date dimension does nothing. To automatically sum up the values of a measure between the selected start and end dates, you would create a calculated measure something like:
SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END DATE].CURRENTMEMBER, [START DATE]))
}, VALIDMEASURE(MEASURES.[MY MEASURE]))
One last thing to notice – since we’re now summing up a range of months rather than returning a single month, query performance might suffer. In which case it might be worth taking a look at my recent entries on tuning YTD-style queries…
Great job!I was searching for a solution to this problem…Many thanksBye
Hi Chris!
I read your article on aggregating values across a date range, but I\’m lost on how to implement this. This is what I want to do:
A.) For a particular Department, Divisions, SubDivision, Provider and Facility I want to sum the number of days between Service_Date and Received_Date.
I attempted to create a calculated member with the following MDX statement. I just simply wanted to get the difference of the two dates regardles of items that I listed in A.). But, it doesn\’t work. I would have expected one big number that represents the difference of the two dates, but I don\’t, I get an error (of course).
I envisoned createing a calculated member in AS2005 and then simply dragging it into my data area, and as I added Department, Division, SubDivision, Provider and Facility dimension information the value of the difference in receive and service date would change accordingly.
Am I dreaming?
This is my MDX any help would be appreciated.
WITH
MEMBER [Measures].[DateDiff] AS
\’iif(ISEMPTY(([Vw Dim Service Date].[Service Sql Date].CurrentMember, [Vw Dim Receive Date].[Receive Sql Date].CurrentMember,[Measures].[Cash_Count]))
,null
, Datediff("d",[Vw Dim Service Date].[Service Sql Date].CurrentMember.Name,[Vw Dim Receive Date].[Receive Sql Date].CurrentMember.Name))\’
SELECT
NON EMPTY {[Vw Dim Service Date].[Service Sql Date].CurrentMember:[Vw Dim Service Date].[Service Sql Date].CurrentMember} ON COLUMNS,
NON EMPTY {[Vw Dim Receive Date].[Receive Sql Date].CurrentMember:[Vw Dim Receive Date].[Receive Sql Date].CurrentMember} * {[Measures].[DateDiff],[Measures].[DateDiff]} ON ROWS
FROM PBG_DW_Charge_Tran_Details
Thanks Chris for the solution..
I noticed after implementing when i tried using both the dimension,, the cel values were always blank except if i select the same values in both the date ranges..
Am i doing anything wrong?..
any help would be greatly appreciated.. thanks,
Note: MDX is as below..
SUM({OPENINGPERIOD([DateDimension].[Day Value], [DateDimension].CURRENTMEMBER):CLOSINGPERIOD([DateDimension].[Day Value], LINKMEMBER([EndDateDimension].CURRENTMEMBER, [DateDimension]))}, VALIDMEASURE([Measures].[ActualMetricValue]))
Sagi – it sounds like your EndDateDimension is connected to your main cube and not just your virtual cube.
Mag – without knowing more about your relational source and cube design it\’s very hard to say, but my gut feeling is that you should be calculating the difference between the two dates in your fact table and then simply creating a normal measure from that column.
Hello,
I\’m implement the same code but need some additional help.
I have to two time dimensions – Time and Time Order Credits
As like the others, when selecting one or the other in the Virtual Cube, data is missing.
I tried using the following but getting errors in my syntax:
VALIDMEASURE( ({[Measures].[Total Amount Credited]}, LINKMEMBER({[Time].CURRENTMEMBER}, {[Time of Order Credts]})) )
I would like to use one time to retrieve all the necessary data, if not the end user will be confused on which time slice to use for all data.
There are several problems with the MDX expression you give, all due to the fact that you\’ve got braces {} where you shouldn\’t have them. So
VALIDMEASURE( ({[Measures].[Total Amount Credited]}, LINKMEMBER({[Time].CURRENTMEMBER}, {[Time of Order Credts]})) )
should be something like
VALIDMEASURE( ([Measures].[Total Amount Credited], LINKMEMBER([Time].CURRENTMEMBER, [Time of Order Credts])) )
I noticed that and corrected but I am getting #ERR as the results. I\’m a little confused on how this is used.
I have a virtual cube that needs both Time dimension tables (Time and Credits Time) However, when choosing one time, I have one data result set and but nothing for the credits and vice versus.
I want to combine both time dimensions via this virtual cube so when the user adds the time dimension to the Pivot table, they are not confused by which time to used but rather can use one and receive the accurate data back.
VALIDMEASURE( ([Measures].[Total Amount Credited], LINKMEMBER([Time].CURRENTMEMBER, [Time of Order Credts])) )
I\’m not sure creating a calculated member will give me the desired results or act as the time dimensions that I am filtering on.
It\’s a bit hard to say what you need to do without seeing your cube, but the approach described in this post is useful when you have one Time dimension that users want to be able to select date ranges on. From the sounds of things you\’ve got two logical time dimensions – do users want to select ranges on both of them?
Reading your post again it makes me wonder whether you actually need to select date ranges at all – it sounds like you have two different Time dimensions coming from two different cubes and want to integrate data from them both into a virtual cube, but then users have to remember to select the same member on both Time dimensions to get sensible data. The best thing to do in this situation would be to change your base cubes so they used the same shared Time dimension; if this isn\’t possible, some calculated measures which used a combination of VALIDMEASURE and LINKMEMBER to \’join\’ the two dimensions would work, but I strongly recommend the former approach.
This is great!
But I\’m using the Excel addin as a client, and this doesn\’t allow me to add my end date dimension as a filter, since there\’s no relation between the dimension and the filter group.
Any suggestions? Many thanks in advance.
Also, since this is a calculation it isn\’t possible to drill through to see the underlying data.
I need to be able to
a) aggregate the measures as usual, but restricted to a range of dates, and
b) for the same filters and date range, see the underlying data including non-measure fields (e.g. a text field)
Is there any way for me to do this?
Re the Excel adding, I seem to remember seeing this last year. Some client tools seem to want to make the assumption that because a dimension doesn\’t have a relationship with a measure group then selecting a member on it won\’t effect the measure values in the measure group alas…
Re drillthrough, if you wanted to be able to do this you\’d have to write an Action which mimicked drillthrough functionality but understood that you were selecting on a date range. Pretty difficult to do, but certainly possible. Take a look at the postings on custom drillthrough on Mark Mrachek\’s blog for some pointers on how you\’d do this: http://www.mrachek.com/
Thanks for the great article, Chris! Helped Heaps.
I know it has been a while since you wrote this, but …
I came across your blog attempting to solve a similar problem to this. I was wondering if you could help me. I have a fact table of multipliers that should be aggregated across a range of dates. So the measure .567 is in my fact table once with a start date of august 16 and an end date of december 3. Is there a way to display that value over each of those days with a calculated measure? I was using something similar to your solution with a min function as opposed to a sum — it filled in the dates, but with the wrong values. Thanks in advance!
Yes, it has been a while – this post was written for Analysis Services 2000!
How exactly do you want your values to be aggregated, and what client tool are you using?
Well, ideally I would like ‘no aggregation’ aggregation if this makes sense. Or a min aggregation as a workaround. I have a table with a start and end date and a multiplier, so when I expand any date in that range, it would display that multiplier. Perhaps I am not properly using the term aggregation.
I built the cube in 2008 and I use mdx through a sproc to access the data. I appreciate your help Chris!
Well, there is a ‘None’ aggregation type you can use for measures although I think it’s only available in Enterprise Edition.
Do the date ranges overlap? If not, then you are better off remodelling your data so that instead of one row for each range, you have one row per date, each with a single date dimension key column and a measure for your multiplier. This would make it very easy to find the multiplier for a given date.
Thanks; I agree that would be easier. The issue is that there are multiple departments each with their own set of 12-30 multipliers. Multipliers are updated typically once a month. Rough estimates say about 100,000 records a year — relatively small. I just figured MDX could afford me the luxury of the additional processing and storage.
I made an argument for our company to go with the enterprise version, but they wouldn’t justify the additional cost.
Perhaps I will see you speak at the PASS summit this year? If not, thank you very much for your help. If you’re ever in Cleveland, let me know and I will buy you a beer.
It’s always better to reduce complexity (especially as far as MDX goes) at the expense of a bit of storage – especially when the volumes are this small.
I’m not sure I will be going to the Summit this year – I’m going to the BA Conference in two weeks, and I try to limit the amount of time I spend travelling for conferences for the sake of my family; I travel too much for work as it is. So maybe some other time…