blogger counters

Monday, May 10, 2010

Sliding Date Ranges with Excel 2010 Pivot Tables

Using an Excel PivotTable to retrieve and graph data from the TFS cube is really easy. And it’s pretty easy to setup date filters for specific date ranges. But what if you want the date range to be a sliding range, such as the past 4 weeks? The answer is with a named set.

If you’re using Excel 2010, there is now a dialog box that allows you to create named sets. Describing a date range that is from today going back N weeks requires a little snippet of MDX (Multi-Dimensional eXpression language). I’ll present the snippet that you need and I’ll explain how to change the number of weeks, days, etc.

Create a Named Set

Here is how to create a named date range set:

  1. Select a cell inside a pivot table bound to the cube so that the PivotTable tools are available
  2. Click the Options tab on the ribbon under the PivotTable Tools section:
    image
  3. Click the Fields, Items & Sets drop-down in the Calculations section of this ribbon tab
  4. Click Manage Sets… in the drop-down
  5. Click New… and then Create Set using MDX…
    image
  6. Enter a name for this set in the Set name text box
  7. Enter the MDX expression that defines the date range (see below)
  8. Click OK

Using a Named Set

Once you’ve defined a named set, it will appear in the PivotTable field list. Where it appears will depend on how you’ve defined in the named set. The sets we’ll define in the next section are all based on the Date dimension, so they’ll appear under that dimension, something like this:

image

You can now use this named set wherever you would use any of the Date dimension’s attributes.

Defining the Named Set

Here is where we get into the actual MDX used to define the named set (that you would enter in step 7 above).

Last 30 Days

For the first example, we’ll create a named set that returns all of the days during the last 30 days. Here is the MDX:

Filter(
    [Date].[Date].[Date],
    [Date].[Date].CurrentMember.Member_Value < Now()
    AND [Date].[Date].CurrentMember.Member_Value >= DateAdd("d", -30, VBA![Date]())
)

Yikes, you say? Well, you can use this without understanding most of what’s inside. The two important pieces are inside the DateAdd function: “d” and –30. The “d” says that we want to add days, and –30 says to go back 30 days. So it includes the last 30 days, plus today (for a total of 31 days).

Last 4 Weeks

Now let’s say that instead of every day, you just want to see the week ending summary. Here is the expression you can use in that case:

Filter(
    [Date].[Year - Week - Date Hierarchy].[Week],
    [Date].[Year - Week - Date Hierarchy].CurrentMember.Member_Value < Now()
    AND [Date].[Year - Week - Date Hierarchy].CurrentMember.Member_Value >=
        DateAdd("ww", -4, VBA![Date]())
)

Here we’ve changed the interval to week (ww) so we get the last four weeks.

14 Comments:

At 3/24/11, 3:46 AM, Anonymous Anonymous said...

I've been trying to do this but can't seem to figure out why the Manage Sets option is disabled. Tried across multiple pivot tables on various sheets yet it's always a disabled option???

Any ideas?

 
At 8/19/11, 8:56 AM, Anonymous Anonymous said...

I too experience the same thing. First I had to make sure the workbook was compatible(click File Tab, then click Convert if it's available) but even with that, the feature is still disabled.

 
At 2/22/12, 3:22 AM, Anonymous Anonymous said...

I too had been searching for the option to be enabled. How can i make the option to be enabled?

 
At 4/27/12, 1:27 AM, Blogger vjmassive said...

John, this is exactly what I am looking for. I know your named set formula is the solution, but as with earlier replies I find the "Manage Sets..." option ghosted.

I want dynamic data for the last seven days & I think it should look like this...

Filter(
[Date].[Date].[Date],
[Date].[Date].CurrentMember.Member_Value < Now()
AND [Date].[Date].CurrentMember.Member_Value >= DateAdd("d", -7, VBA![Date]())
)

but I don't understand how to access the option

 
At 8/2/12, 11:48 PM, Anonymous Anonymous said...

If the "Manage Sets ..." option is ghosted, then your pivot table is not based on an OLAP datatable. Use the PowerPivot add-in to create the OLAP pivot.

 
At 9/4/12, 8:13 PM, Anonymous Anonymous said...

Hi John,

Another happy person looking for exactly this functionality. I am all good with creating the Managed Set, but once it is created (and successfully tested), when I close Set Manager, I do not see it in my PivotTable Fields List. Any help would be appreciated.

Thanks

Mark

 
At 10/19/12, 7:01 AM, Anonymous Anonymous said...

How do I accomplish this when trying to look at dates that are not at the top of the hierarchy such as createdDate or ResolvedDate in TFS

 
At 11/8/12, 7:23 AM, Anonymous Anonymous said...

Hello,

Is there a way to use these settings to make a date range filter, with start date and end date?
My users need to filter this way, example:

See the orders made between '2012-10-01' and '2012-10-31', where those dates are inputed by the users.

Thanks!

 
At 11/19/12, 9:45 PM, Anonymous Anonymous said...

Perfect! Thanks! Just what I was looking for. You saved me a bunch of time...

 
At 1/29/13, 7:53 AM, Anonymous Anonymous said...

You can accomplish something similar f your pivot's source is in a table: create another column in your table and use a formula to define your Date\Age buckets. Refresh your pivot and add your new column to the pivot's Report Filter. This option allows you to setup 2010 Slicers as well as use the filtered value in the Pivot\Pivot Chart Title. No vba needed or fancy pants powerpivot plugin.

 
At 10/17/13, 8:54 AM, Anonymous Anonymous said...

Thank you so much, so usefull !
Example for the TFS cube :
Filter(
[Work Item].[System_CreatedDate__HierarchyByMonth].[Month],
[Work Item].[System_CreatedDate__HierarchyByMonth].CurrentMember.Member_Value < Now()
AND [Work Item].[System_CreatedDate__HierarchyByMonth].CurrentMember.Member_Value >=
DateAdd("m", -4, VBA![Date]())
)

 
At 11/27/13, 11:36 AM, Anonymous Anonymous said...

I came across your script and I would like to use it in Powerpivot that is connected to a some sql tables.

Unfortunately, when I create the managed set and place in the rows, I get no information under the rows and my values are gone.

Here is my code....
Filter(
[DimDate].[FullDate],
[DimDate].[FullDate].CurrentMember.Member_Value < Now()
AND [DimDate].[FullDate].CurrentMember.Member_Value >= DateAdd("d", -120, VBA![Date]())
)

 
At 1/9/14, 5:26 AM, Anonymous ms excel consultants said...

Your blog is awfully appealing. Your workshop is tremendous. I desire to be present there. I am contented with your post. I will visit you for more update. Thanks!

 
At 4/11/14, 2:56 PM, Anonymous Anonymous said...

Very useful post! I thought I would share an problem I had resulting from a bad [Month] attribute design. I initially had problems when trying to use [Dates].[Year Month].[Month]. Turns out my [Month] attribute's property needed a date type in the ValueColumn because I used a char value in it's NameColumn. Once I resolved this, I was able to get the prior 3 months with the following:

Filter(
[Dates].[Year Month].[Month],
[Dates].[Year Month].CurrentMember.Member_Value < DateAdd("m", -1, VBA![Date]())
AND
[Dates].[Year Month].CurrentMember.Member_Value >= DateAdd("m", -4, VBA![Date]())
)

 

Post a Comment

Links to this post:

Create a Link

<< Home