I have a simple Insurance cube with 5 dimensions - 2 of which I need in a calculation

  • ClaimStatus - Can be Open, Closed, Pending etc
  • DateOpened - A simple time dimension Year, Quarter, Month, Day

I have 1 calculated member working fine under the ClaimStatus dimension. I have the following to give all OPEN claims opened in 2007 which works...

SUM( { ( [ClaimStatus].[Claim Status Description].[OPEN], [Date Opened].[Year].[2007] ) } )

I am trying to build a set/member to give all OPEN claims not opened in 2007 & use the following, but this does not work...

SUM({[ClaimStatus].[Claim Status Description].[OPEN], EXCEPT([Date Opened].[Year].members, {[Date Opened].[2007]} )})

This passes the syntax check, but always generates an #ERR in cells in the cube. Can't find anything on Google to help here & have also tried to use this as a Named Set but to no avail.