Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Different aggreation results with and without a dummy WITH clause Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 6:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
Thanks. Chris has given some input on another aspect of this problem in another forum and I am following this up with him too.

--Matt.
Post #1429549
Posted Tuesday, March 12, 2013 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:45 AM
Points: 3, Visits: 24
The problem is almost certainly the part of the calculation that uses IsError(); when you say

[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)

What you're actually doing is checking whether the value of [Measures].[Net Lost Commitments (during period)] before you made the scoped assignment contains the error or not, which I don't think is what you wanted.

If you want to check for multi-select, what you should do is something like this:

[Measures].[Net Lost Commitments (during period)] =
iif(count(existing [Date Dimension].[Fiscal Year Hierarchy].Members)>1

HTH,

Chris
Post #1429628
Posted Tuesday, March 12, 2013 9:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
Wow, so iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember) does not test whether the CurrentMember function returns an error? MDX is still very counter-intuitive to me in many ways.

Thanks for offering the alternative formulation. Over the past few days I have re-arranged measure definitions so that the need to check for more-than-one date range is no longer necessary. So the problem has (well, seems to have) gone away.

--Matt.
Post #1430194
Posted Wednesday, March 13, 2013 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:45 AM
Points: 3, Visits: 24
IsError() tests whether a cell value contains an error, not whether a function returns an error. That might happen when CurrentMember returns an error, but it's not quite the same thing.

Chris
Post #1430273
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse