Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Aggregating values across a date range

with 18 comments

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… 

Written by Chris Webb

January 5, 2005 at 1:35 pm

Posted in Analysis Services

18 Responses

Subscribe to comments with RSS.

  1. Great job!I was searching for a solution to this problem…Many thanksBye

    giorgioa

    September 21, 2005 at 11:20 am

  2. 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
     

    Unknown

    February 28, 2006 at 1:47 am

  3. 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]))
     

    Prabhu

    March 7, 2006 at 12:29 am

  4. 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.

    Chris

    March 7, 2006 at 11:37 am

  5. 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.

    Sonya

    June 6, 2006 at 7:17 pm

  6. 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])) )
     

    Chris

    June 7, 2006 at 7:09 am

  7. 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.
     

    Sonya

    June 7, 2006 at 9:13 pm

  8. 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.

    Chris

    June 10, 2006 at 7:48 am

  9. 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.

    Unknown

    June 20, 2006 at 3:01 pm

  10. 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?

    Unknown

    June 20, 2006 at 3:42 pm

  11. 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/

    Chris

    June 23, 2006 at 7:23 am

  12. Thanks for the great article, Chris! Helped Heaps.

    Kho Wei

    January 6, 2011 at 4:10 am

  13. 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!

    Ehren

    March 26, 2013 at 10:37 pm

    • 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?

      Chris Webb

      March 26, 2013 at 10:42 pm

      • 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!

        Ehren

        March 27, 2013 at 1:56 pm

      • 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.

        Chris Webb

        March 27, 2013 at 4:18 pm

      • 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.

        Ehren

        March 27, 2013 at 5:07 pm

      • 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…

        Chris Webb

        March 27, 2013 at 5:47 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,071 other followers

%d bloggers like this: