Date Dimension Filtering

  • Hello,

    I have a date dimension [Facility].[Close Date] which I need to filter on. The criteria for the filter is to keep any records with [Facility].[Close Date].&[1899-12-30T00:00:00] or any record that has a [Facility].[Close Date] that is greater than a date I am passing into the query that represents the start of a reporting period. I have tried several different approaches to this including various variations on filter(), conditional statements within the filter, sub queries and various date parsing techniques to no avail. Can someone please advise me as I am brand new to MDX and have run out of ideas.

    Thanks,

    Jay Paolucci

  • Post your query, so someone may help you easier.

    Igor Micev,My blog: www.igormicev.com

  • In this case I believe it would be easier and better to include logic in your fact table about whether or not the facility was closed at the time of the fact entry, and have a separate dimension to represent that.

    Doing this in MDX gets tricky because it doesn't really treat dimension data according to the raw data coming in. It treats it based on the hierarchies you define. There are some MDX functions that are specific to date dimensions and implement some of the logic, but its not so simple as saying give me dates greater than a certain one. In this case you need to tell it to give you a specific member and every member after it, plus one other member.

    That ought to be possible, but is pretty convoluted. And I don't think the mdx query should be doing the heavy lifting here. If pressed, I'd probably use LastPeriods and Prevmember to get all date members before your report date, then use except on a set of all the date members to get only the ones I wanted, and then add the default date in to the set. And assuming you didn't want to actually split up the measure by closing date, I would do all that in a calculated member within an aggregate. I think.

    As I said, the much cleaner solution is to have a Facility Status dimension (with open or closed) and have code in the view defining the fact table populating it.

  • Here is a simplified example of what I'm trying to do. It uses the where filter that I've been trying to get to work:

    select [Measures].[Is Exited] on 0,

    ([Facility].[Close Date].children) on 1

    FROM [HUMANRESOURCES]

    where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) or ([Facility].[Close Date].&[1899-12-30T00:00:00])))

  • Jay Paolucci-371454 (7/22/2014)


    Here is a simplified example of what I'm trying to do. It uses the where filter that I've been trying to get to work:

    select [Measures].[Is Exited] on 0,

    ([Facility].[Close Date].children) on 1

    FROM [HUMANRESOURCES]

    where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) or ([Facility].[Close Date].&[1899-12-30T00:00:00])))

    Does the following query return rows?

    select [Measures].[Is Exited] on 0,

    ([Facility].[Close Date].children) on 1

    FROM [HUMANRESOURCES]

    where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) ))

    Are there rows for this date [Facility].[Close Date].&[1899-12-30T00:00:00] ?

    Igor Micev,My blog: www.igormicev.com

  • That does not return rows, but it appears that it should as there is a 03/31/2014 close date. Could this be a date format issue? The designer of the cube is representing no close date as [Facility].[Close Date].&[1899-12-30T00:00:00], so there are many records with that as the close date value.

  • Jay Paolucci-371454 (7/23/2014)


    That does not return rows, but it appears that it should as there is a 03/31/2014 close date. Could this be a date format issue? The designer of the cube is representing no close date as [Facility].[Close Date].&[1899-12-30T00:00:00], so there are many records with that as the close date value.

    Definitely, you should check it out.

    Igor Micev,My blog: www.igormicev.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply